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

Question

I have a migration where I create a products table like so

class CreateProducts < ActiveRecord::Migration
  def change
    create_table :products do |t|
      t.string :name
      t.hstore :data

      t.timestamps
    end
  end
end

On the activerecord-postgres-hstore page they add an index to the table (in SQL) with

CREATE INDEX products_gin_data ON products USING GIN(data);

However that change is not tracked by migrations (I'm guessing because it's Postgres specific?), is there a way to create an index from within a migration?

thanks!

Scarlet

Answer 1

Yes! You can make another migration and use the 'execute' method... like so:

class IndexProductsGinData < ActiveRecord::Migration
  def up
    execute "CREATE INDEX products_gin_data ON products USING GIN(data)"
  end

  def down
    execute "DROP INDEX products_gin_data"
  end
end

UPDATE: You might also want to specify this line in config/application.rb:

config.active_record.schema_format = :sql

You can read about it here: http://apidock.com/rails/ActiveRecord/Base/schema_format/class

Bola

Perfect, thanks for the tip on config.active_record.schema_format – Scarlet

Additionally you may want to make it: CREATE INDEX CONCURRENTLY products_gin_data ON products USING GIN(data) this would allow it to not lock the table while its adding the index. – Finley

You can also use CONCURRENTLY on the DROP. – Rudin

Answer 2

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

    add_index :products, :data, using: :gin

Polyester

This does not generate the GIN(data). Might be bug in the generator. – Tourism

gave me this in my structure.sql (Rails 4.0.2): CREATE INDEX index_products_on_data ON products USING gin (data); – Polyester