Rails Postgres功能索引

用于创建索引的内置ActiveRecord方法( add_index )不支持功能或任何其他更高级的功能。相反,你可以使用 execute 使用SQL创建索引:

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

注意使用 执行 如果不使用SQL架构格式,则迁移中可能存在问题。( config.active_record.schema_format = :sql )有关详细信息,请搜索 schema_format .

我通过移除一对护栏,可以从Rails(3.1.3)迁移中获取功能索引!

  # 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

不过,我必须自己制作索引名:

  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

(您可能不需要在索引列名周围加引号,除非您正在做一些疯狂的事情,例如在其中添加空格或奇怪的字符。)

(在尝试回滚不存在的索引时,可能可以使用Postgres错误消息。)

如果你使用宝洁能量宝石( https://github.com/TMXCredit/pg_power ) 您可以按以下方式执行:

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