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

score 18 · Accepted Answer · edited May 23 '17 at 12:33

18

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.

edited May 23 '17 at 12:33

Community

1
1

answered Jul 17 '10 at 12:37

Jason Weathered

7,762
2
41
37

To make this migration reversible, simply add to `down`: `remove_index :temporary_events, :name => "temporary_events_campaign_tinfoid_date_messagetype"` – Oskar Holmkratz Jan 28 '17 at 07:43

bonkydog · Answer 2 · 2011-12-12T23:58:15.840

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

score 1 · Answer 3 · answered Jan 21 '14 at 11:56

1

If you are using pg_power gem(https://github.com/TMXCredit/pg_power), you can do it in the following way:

add_index(:comments, 'dmetaphone(author)', :using => 'gist')

answered Jan 21 '14 at 11:56

Sergey Potapov

3,819
3
27
46

The repo has been removed. The gem is not maintained since 2014. There is, probably a copy/fork of the original repo at: https://github.com/sfate/pg_power – Vajk Hermecz Nov 06 '20 at 10:18

Rails Postgres functional indexes

3 Answers3

Linked