Migration of trigram search in Rails

Question

I have a migration:

class AddGinIndexToContacts < ActiveRecord::Migration
  def up
    execute("CREATE INDEX contacts_search_idx ON contacts USING gin (first_name gin_trgm_ops, last_name gin_trgm_ops, name gin_trgm_ops)")
  end

  def down
    execute("DROP INDEX contacts_search_idx")
  end
end

It generates this code in schema.rb:

add_index "contacts", ["first_name", "last_name", "name"], name: "contacts_search_idx", using: :gin

and later, when I execute rake db:schema:load it generates wrong sql:

CREATE  INDEX  "contacts_search_idx" ON "contacts" USING gin ("first_name", "last_name", "name")

Firstly, it says:

ERROR: data type character varying has no default operator class for access method "gin"

Secondly, there are lost gin_trgm_ops.

How to make it works?

Rails 4.2

score 4 · Answer 1 · answered Apr 15 '15 at 19:18

4

You can use the order param to define the operator class.

add_index "contacts", ["first_name", "last_name", "name"], name: "contacts_search_idx", using: :gin, order: {first_name: :gin_trgm_ops, last_name: :gin_trgm_ops, name: :gin_trgm_ops}

Found this solution here: http://apidock.com/rails/ActiveRecord/ConnectionAdapters/SchemaStatements/add_index#1553-Adding-index-with-other-operator-classes-PostgreSQL-

answered Apr 15 '15 at 19:18

Marcus Derencius

94
4

1

Thank you, but it still generates wrong schema.rb without gin_trgm_ops – Dmitry Silchenko Apr 17 '15 at 12:18

score 2 · Answer 2 · edited May 23 '17 at 12:01

2

Unfortunately, this cannot be solved unless schema is changed to sql. Few links:

Rails and Postgres Hstore: Can you add an index in a migration?

Why does add_index using 'gin' create a 'btree' index instead?

Once I have changed schema format to sql it works fine.

edited May 23 '17 at 12:01

Community

1
1

answered Apr 17 '15 at 12:18

Dmitry Silchenko

73
1
5

You should include the important information from the links, and add them as reference. Please have a look at the help center – NatureShade Apr 17 '15 at 12:41

score 1 · Answer 3 · answered Jun 15 '18 at 11:20

1

You can nowadays use the opclass option:

add_index :contacts, [:first_name, :last_name, :name], name: "contacts_search_idx", using: :gin, opclass: { first_name: :gin_trgm_ops, last_name: :gin_trgm_ops, name: :gin_trgm_ops }

answered Jun 15 '18 at 11:20

Ivan Navarrete

536
1
5
14

It's showing me an error like this 'Unknown key: :opclass. Valid keys are: :unique, :order, :name, :where, :length, :internal, :using, :algorithm, :type' – NAREN PUSHPARAJU Jun 05 '19 at 14:55
1

NOTE: `opclass` option requires Rails 5.2+ – Yarin Oct 12 '19 at 19:52

score 0 · Answer 4 · answered Jan 25 '16 at 11:30

0

You need to install the btree_gin Postgres extension in order to use GIN indexes.

Simply add enable_extension('btree_gin') to the migration and it all should work fine.

answered Jan 25 '16 at 11:30

mrstif

2,736
2
27
28

Migration of trigram search in Rails

4 Answers4