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 = 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:

  • use the citext extension
  • use ILIKE instead of LIKE
  • 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.

Growing Rails Applications in Practice
Check out our new e-book:
Learn to structure large Ruby on Rails codebases with the tools you already know and love.

Owner of this card:

Avatar
Tobias Kraze
Last edit:
over 2 years ago
by Thomas Eisenbarth
About this deck:
We are makandra and do test-driven, agile Ruby on Rails software development.
License for source code
Posted by Tobias Kraze to makandra dev
This website uses cookies to improve usability and analyze traffic.
Accept or learn more