Case insensitive index in postgres, handles case sensitive queries?
indexing, pattern-matching, postgresql, sql
Solution
The new index can only be used with conditions based on the index expression:
...
WHERE lower(email) = 'abc@foo.org' -- 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.
See:
- PostgreSQL LIKE query performance variations
- LOWER LIKE vs iLIKE
Problem
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?