Case-insensitive unique index in Rails/ActiveRecord?

Since MySQL indexes are already case-insensitive, I’m guessing you’re dealing with PostgreSQL, which creates case-sensitive indexes by default. I’m answering here based on Rails 3.2.3 and PostgreSQL 8.4.

It seems functional indexes are one more example of things that ActiveRecord can’t generate. Foreign keys and UUID columns are two more that come to mind. So there is no choice (other than monkey-patching ActiveRecord) but to use execute statements.

This means for an accurate dump of your database, you’ll need to abandon the DB-agnostic schema.rb in favor of DB-specific structure.sql. See the Rails Guide on Migrations, section 6.2 Types of Schema Dumps. This is set as follows:

config/application.rb

config.active_record.schema_format = :sql

db/structure.sql should be updated automatically when you run a migration. You can generate it manually with this command:

rake db:structure:dump

The file is pure Postgres SQL. Although not documented when you use rake -T to list rake tasks, it seems that you can use this command to load the database from the structure.sql dump:

rake db:structure:load

There’s nothing magic here: the source code (shown here from Rails 3.2.16) just calls psql on structure.sql.

Finally, here is my migration to drop an old, case-sensitive email constraint and add the case-sensitive functional index:

class FixEmailUniqueIndexOnUsers < ActiveRecord::Migration
  def up
    remove_index :users, :email
    execute "CREATE UNIQUE INDEX index_users_on_lowercase_email 
             ON users USING btree (lower(email));"
  end

  def down
    execute "DROP INDEX index_users_on_lowercase_email;"
    add_index :users, :email, :unique => true
  end
end