LOWER indexes in schema.rb

Question

In my Rails application, which, using a 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 to this column. I do this with the following migration:

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

works as expected, so in the circuit I have the following index:

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

But I want 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 unfortunately this is not visible in schema.rb? I know that I can use the SQL format instead of schema.rb, but I want to stick with 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 used this data type in many applications where the string case is unimportant, for example. user email address. This allows you to respond to queries in this column, even if the rows have different cases. This also affects uniqueness constraints, so two lines with the same sequence of characters that have different cases are now considered the same.

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

change_column :person_identifiers, :identifier, :citext

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