Rails 4 pgsql add_index with type GIN or GiST

Question

In Rails it is possible to do:

add_index :table, :column_name, :using => 'btree'

Is it possible in Rails 4 with PGSQL to add a GIN or GiST index like:

add_index :students, :name, :using => 'gin'

Or do I have use manual execute statements? The notion behind this is I would like to keep schema.rb instead of using structure.sql

score 58 · Accepted Answer · answered Sep 10 '13 at 20:40

58

In Rails 4, you can now do something like this in a migration:

add_index :products, :data, using: :gin

answered Sep 10 '13 at 20:40

Pronix

938
8
8

1

Is there any docs on this? I saw a similar answer on Stackoverflow, but `add_index :products, :data, using: :gin` raises an exception when used in a migration. – Bill Sep 11 '13 at 14:04
check code - it simple sql request [add_index](http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-add_index) – Pronix Sep 17 '13 at 09:26
11

This raises an error `PG::UndefinedObject: ERROR: data type character varying has no default operator class for access method "gin"` ... and this is on Rails 4.2. – King'ori Maina Aug 05 '15 at 08:24
I had the same error, got it working this command in my migration: `execute("CREATE INDEX some_name ON products USING gin(to_tsvector('data'))")` – panmari Aug 22 '15 at 14:58
1

Small correction, you also need to give a configuration as attribute, e. g. 'english': `execute("CREATE INDEX some_name ON products USING gin(to_tsvector('english', 'data'))")` – panmari Aug 22 '15 at 15:05
Here a related question http://stackoverflow.com/questions/34676731/not-able-to-set-special-index-type-options-lower-unaccent-etc-for-gin-and-gi – phlegx Jan 08 '16 at 12:11
Note that `'gin'` as a string like in the question, also works. – Dennis Mar 17 '16 at 22:03
Gin indexes only work on array fields. Change the table definition first – bluehallu Aug 25 '16 at 17:45
4

@King'oriMaina In Rails 5 you can use the `opclass ` argument to use a default operator class and avoid that error: `add_index :products, :data, using: :gin, opclass: :gin_trgm_ops` https://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-add_index – Scott Bartell Apr 25 '19 at 17:07
1

Add `enable_extension "btree_gin"` in the migration above this step. – Haseeb A Oct 06 '21 at 07:16
@HaseebA btree_gin is not the same thing as trigram indexing, and is relatively useless for string matching in my experience. Scott Bartell has the correct idea to set an operator. If your version does not support an operator, you will need to use SQL – Sampson Crowley Nov 24 '21 at 07:09

score 35 · Answer 2 · answered Dec 04 '15 at 00:07

Wow! I had a few hairs turn gray on this one. I am using rails 4.2 and trying to run this migration and it was giving me the same error as people above.

PG::UndefinedObject: ERROR: data type character varying has no default

I found out that you can in fact still continue to use schema.rb and do not have to use the config/application.rb

config.active_record.schema_format = :sql

The one major thing I was missing was installing postgres contrib modules. I made the assumption that features like gin and gist were already turned on. I never noticed but modules are shown in your schema.rb file. They appear at the top like this

ActiveRecord::Schema.define(version: 20151203234708) do

  # These are extensions that must be enabled in order to support this database
  enable_extension "plpgsql"
  enable_extension "pg_trgm"
  enable_extension "fuzzystrmatch"
  enable_extension "btree_gin"
  enable_extension "btree_gist"

if you do not see btree_gin enabled, you can not use the code

add_index :products, :data, using: :gin

you can install any module by running a migration like so. The changes will be reflected in your schema.rb

class InstallSomeContribPackages < ActiveRecord::Migration
  def up
    execute "CREATE EXTENSION IF NOT EXISTS btree_gin;"
    execute "CREATE EXTENSION IF NOT EXISTS btree_gist;"
  end

  def down
    execute "DROP EXTENSION IF EXISTS btree_gin;"
    execute "DROP EXTENSION IF EXISTS btree_gist;"
  end
end

here is a list of postgres modules

You can also add extensions in migrations with migration syntax http://stackoverflow.com/questions/16611226/how-to-install-postgres-extensions-at-database-creation `enable_extension "btree_gin"` — mmrobins, Feb 09 '16 at 22:45
And I would also note, that `btree_gin` is enough, at least for PostgreSQL 9.4 — denis.peplin, Aug 26 '16 at 14:02

Rails 4 pgsql add_index with type GIN or GiST

2 Answers2