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

Pronix · Accepted Answer · 2013-09-10 20:40:37Z

61

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

add_index :products, :data, using: :gin

answered Sep 10, 2013 at 20:40

Pronix

9678 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Bill Over a year ago

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.

King'ori Maina Over a year ago

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.

panmari Over a year ago

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'))")

Scott Bartell Over a year ago

@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 api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/…

Haseeb A Over a year ago

Add enable_extension "btree_gin" in the migration above this step.

|

Josh · Accepted Answer · 2015-12-04 00:07:17Z

35

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

answered Dec 4, 2015 at 0:07

Josh

5995 silver badges19 bronze badges

2 Comments

mmrobins Over a year ago

You can also add extensions in migrations with migration syntax stackoverflow.com/questions/16611226/… enable_extension "btree_gin"

denis.peplin Over a year ago

And I would also note, that btree_gin is enough, at least for PostgreSQL 9.4

Collectives™ on Stack Overflow

Rails 4 pgsql add_index with type GIN or GiST

2 Answers 2

11 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related