LOWER indexes in schema.rb

Question

In my Rails app that, using PostgreSQL database, I have a table that looks like this:

create_table "person_identifiers", force: :cascade do |t|
  t.string "identifier", limit: 255
end

I want to add an index on that column. I do this with following migration:

execute('create index person_identifiers_identifier on person_identifiers using gin (identifier gin_trgm_ops)')

that is working as expected so in schema I have the following index:

add_index "person_identifiers", ["identifier"], name: "person_identifiers_identifier", using: :gin

But I want to have this index to be case insensitive so I write:

execute('create index person_identifiers_identifier on person_identifiers using gin (lower(identifier) gin_trgm_ops)')

but that's unfortunately is not visible in schema.rb? I know that I can use SQL format instead of schema.rb, but I want to stick to see this in schema.rb.

Answer 1

A similar question has already been asked and answered. However, PostgreSQL provides a case-insensitive text data type, called citext. I've used this data type in many applications where the case of the string is unimportant, e.g. user's email addresses. This allows queries on that column to match, even if the strings are different cases. It also affects uniqueness constraints, so two strings with the same character sequence that have different cases are now considered the same.

To use citext in Rails, you will need the activerecord-postgresql-citext gem (this requires Rails version 4). Once the gem is installed, you can migrate the column to citext using

change_column :person_identifiers, :identifier, :citext

Keep in mind this is a case-insensitive text column, not a string column.