[Proposal] Case Insensitive Unique Indexes

rubyonrails-core
1

Unless this exists and I can’t find it, this is a feature proposal/discussion.

I’m currently writing a migration and I’d like to end up with a case-insensitive unique index on a string column. The purpose is to mirror/enforce the following validation in the db:

validates :name, uniqueness: {case_sensitive: false}

Using postgres, it would look like this:

CREATE UNIQUE INDEX index_name ON table_name (UPPER(column_name));

UPPER or LOWER doesn’t matter, it’s used only in the index. Refer to Postgres docs

Putting this in a migration might look like this:

add_index :table, [:name], unique: true, case_sensitive: false

Probably ignore_case would only be valid if unique were true. Or we could use a unique_ignore_case

Thoughts on such a feature?

6 Likes
2

Would have to be able to handle composite indexes.

3

Looks like it’s already supported, you can apparently do:

add_index :table, 'upper(name)', unique: true

It’s referred to as “expression column name”.

I found this blog post announcing the feature in Rails 5.

However, it’s not mentioned anywhere in the doc, while definitely existing on main branch.

I vote for documenting this.

1 Like