Case insensitive index in postgres, handles case sensitive queries?

Question

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 performance would be equivalent?

Answer 1

The new index can only be used with conditions based on the index expression:

...
WHERE lower(email)  = '[email protected]' -- search string in lower case.

As long as you remember that, you can drop the old case sensitive index.

Or you have a look at trigram indexes which work case insensitive to begin with.

Related:

• PostgreSQL LIKE query performance variations
• LOWER LIKE vs iLIKE