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

Community · Accepted Answer · 2017-05-23 12:33:32Z

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, 2017 at 12:33

CommunityBot

11 silver badge

answered Jul 17, 2010 at 12:37

Jason Weathered

7,8112 gold badges43 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Oskar Holmkratz Over a year ago

To make this migration reversible, simply add to down: remove_index :temporary_events, :name => "temporary_events_campaign_tinfoid_date_messagetype"

bonkydog · Accepted Answer · 2011-12-12 23:58:15Z

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

Serhii Potapov · Accepted Answer · 2014-01-21 11:56:06Z

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, 2014 at 11:56

Serhii Potapov

4,0604 gold badges34 silver badges47 bronze badges

1 Comment

Vajk Hermecz Over a year ago

The repo has been removed. The gem is not maintained since 2014. There is, probably a copy/fork of the original repo at: github.com/sfate/pg_power

Collectives™ on Stack Overflow

Rails Postgres functional indexes

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related