← Back to Upcase

Case insensitive unique indexes in postgres


(Justin Gordon) #1

If I change a regular unique index on users.email to a case insensitive one by creating this index:

CREATE UNIQUE INDEX user_email_ci_idx ON users ((lower(email)));

Any reason not to drop the prior case sensitive index?

I would guess that postgres will switch to the new index, and the resulting results set would be equivalent in terms of performance.

On a related note, do others use case insensitive unique indexes for users.email?


(Justin Gordon) #2

It turns out that Devise is already setting emails to lowercase in a before_validation hook. So instead of changing the index, I need to change all my queries to find users by email to downcase the query value.