Rails Postgres functional indexes

Question

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

Answer 1

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.

Answer 2

I was able to get functional indexes out of Rails (3.1.3) migrations by removing a couple guard-rails!

  # lib/functional_indexes.rb
  module ActiveRecord
    module ConnectionAdapters
      module SchemaStatements
        #disable quoting of index columns to allow functional indexes (e.g  lower(full_name) )
        def quoted_columns_for_index(column_names, options = {})
          column_names
        end

        def index_name_for_remove(table_name, options = {})
          index_name = index_name(table_name, options)

          # disable this error check -- it can't see functional indexes
          #unless index_name_exists?(table_name, index_name, true)
          #  raise ArgumentError, "Index name '#{index_name}' on table '#{table_name}' does not exist"
          #end

          index_name
        end
      end
    end
  end

I had to make my own index names, though:

  class AddLowerCaseIndexes < ActiveRecord::Migration
    def up
      add_index :people, 'lower(full_name)', :name => "index_people_on_lower_full_name"
      add_index :people, 'lower(company)',   :name => "index_people_on_lower_company"
    end

    def down
      remove_index :people, :name => "index_people_on_lower_full_name"
      remove_index :people, :name => "index_people_on_lower_company"
    end
  end

(You probably don't need quotes around your index column names unless you are doing something insane like putting spaces or weird characters in them.)

(You are probably fine with postgres error messages when attempting to rollback non-existent indexes.)