Posted over 2 years ago. Visible to the public. Repeats.
Case sensitivity in PostgreSQL
PostgreSQL, unlike MySQL, treats strings as case sensitive in all circumstances.
This includes
- comparison with
=andLIKE - collision detection in unique indexes
Usually this is fine, but some strings (like emails and usernames) should typically be treated as case insensitive.
There are a few workarounds available:
- use the citext extension
- use
ILIKEinstead ofLIKE - use Postgres'
lower()function - add an index on
lower(email)
Probably the best approach is to just convert emails (and usernames) to lowercase within Rails. Popular authentication libraries (like Clearance and Devise) already do this for you.
Once an application no longer requires constant development, it needs periodic maintenance for stable and secure operation. makandra offers monthly maintenance contracts that let you focus on your business while we make sure the lights stay on.
