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

Hardihood

Answer 1

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-

Swirsky

Thank you, but it still generates wrong schema.rb without gin_trgm_ops – Hardihood

Answer 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.

Hardihood

You should include the important information from the links, and add them as reference. Please have a look at the help center – Logan

Answer 3

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 }

Ronald

It's showing me an error like this 'Unknown key: :opclass. Valid keys are: :unique, :order, :name, :where, :length, :internal, :using, :algorithm, :type' – Anaconda

NOTE: opclass option requires Rails 5.2+ – Binnings

Answer 4

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.

Reposit