Case sensitivity in PostgreSQL

Updated . Posted . Visible to the public. Repeats.

PostgreSQL, unlike MySQL, treats strings as case sensitive in all circumstances.

This includes

  • comparison with = and LIKE
  • 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:

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.

Tobias Kraze
Last edit
Tobias Kraze
License
Source code in this card is licensed under the MIT License.
Posted by Tobias Kraze to makandra dev (2016-08-01 10:06)