Rails Postgres functional indexes

indexing, postgresql, ruby-on-rails, sql

Solution

The built-in ActiveRecord method for creating indexes (`add_index`) does not support functions or any other more advanced features. Instead you can use `execute` to create the index with SQL:

execute <<-SQL
  CREATE INDEX temporary_events_campaign_tinfoid_date_messagetype
  ON temporary_events(templateinfoid, campaign, date(gw_out_time), messagetype);
SQL

Note that the use of `execute` in migrations can be problematic if you are not using the SQL schema format (`config.active_record.schema_format = :sql`). For more information, search for schema_format.

Problem

How I should enter my multicolum indexes which contain functions into schema.rb ? for example this DOESN'T work: ``` add_index "temporary_events", ["templateinfoid", "campaign", "date(gw_out_time)", "messagetype"], :name => "temporary_events_campaign_tinfoid_date_messagetype" ``` rake db:test:load rake aborted! PGError: ERROR: column "date(gw_out_time)" does not exist : CREATE INDEX "temporary_events_campaign_tinfoid_date_messagetype" ON "temporary_events" ("templateinfoid", "campaign", "date(gw_out_time", "messagetype")

Original source