Case-insensitive unique index in Rails/ActiveRecord?

Question

I need to create a case-insensitive index on a column in rails. I did this via SQL:

execute(
   "CREATE UNIQUE INDEX index_users_on_lower_email_index 
    ON users (lower(email))"
 )

This works great, but in my schema.rb file I have:

add_index "users", [nil], 
  :name => "index_users_on_lower_email_index", 
  :unique => true

Notice the "nil". So when I try to clone the database to run a test, I get an obvious error. Am I doing something wrong here? Is there some other convention that I should be using inside rails?

Thanks for the help.

Answer 1

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

Answer 2

If you are using PostgreSQL you can change your column type to citext - case-insensitive string. It also makes search independent from the register.

def change
  enable_extension :citext
  change_column :users, :email, :citext
  add_index :users, :email, unique: true
end

Answer 3

I would simplify this...

In your model:

before_validation :downcase_email

def downcase_email
  self.email = email.downcase
end

That way, the index is database agnostic, and your emails are all lowercase in the database.