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.

Answer 4

Have you considered using schema_plus (https://github.com/lomba/schema_plus)? Among other things (support for enforcing foreign keys in the database and for views), it supports setting case-insensitive indexes for PostgreSQL databases and handles dumping them in the schema. From the Readme, "If you’re using Postgresql, SchemaPlus provides support for conditions, expressions, index methods, and case-insensitive indexes."

Answer 5

The documentation is unclear on how to do this but the source looks like this:

def add_index(table_name, column_name, options = {})
  index_name, index_type, index_columns = add_index_options(table_name, column_name, options)
  execute "CREATE #{index_type} INDEX #{quote_column_name(index_name)} ON #{quote_table_name(table_name)} (#{index_columns})"
end

So, if your database's quote_column_name is the default implementation (which does nothing at all), then this might work:

add_index "users", ['lower(email)'], :name => "index_users_on_lower_email_index", :unique => true

---

You note that you tried that one but it didn't work (adding that to your question might be a good idea). Looks like ActiveRecord simply doesn't understand indexes on a computed value. I can think of an ugly hack that will get it done but it is ugly:

1. Add an email_lc column.
2. Add a before_validation or before_save hook to put a lower case version of email into email_lc.
3. Put your unique index on email_lc.

That's pretty ugly and you might feel dirty for doing it but that's the best I can think of right now.

Answer 6

For Rails 4.2, create case-insensitive unique index in users table on name column.

Create new migration file with empty change method:

$ rails generate migration add_index_in_users_on_name

Add call of add_index method to empty change method:

add_index :users, 'lower(name)', name: 'index_users_on_lower_name', unique: true

Run Rake db:migrate task:

$ rake db:migrate

In result, index will be added correctly and file db/schema.rb contains correct add_index:

add_index "users", ["LOWER(\"NAME\")"], name: "index_users_on_lower_name", unique: true

This tested only with RDB Oracle.

Answer 7

I would suggest (just as an opportunity to consider among others) to use two separate fields:

• email
• email_original

The first one is always down cased and thus can be uniquely indexed in a database agnostic manner, while the second field keeps verbatim to what user enetered and can have upper characters.

Obviously in User model one then needs to set email based on email_original on each save and prohibit direct mangling with email field.

Answer 8

I think you need column name(s) as below

   add_index "users", [email], {:name => "index_users_on_lower_email_index", :unique => true }

And you have to make email field in database with proper Case Insensitive collation, that way your index will be also case insensitive.

depending of db engine which you are using it syntax may be different but

alter table [users] alter column [email] varchar(250) collate utf8_general_ci ...

and when you add index to this column it will be case insensitive.