When using an email address to login to a web site the email address should be treated as case-insensitive. This typically results in writing code/SQL that includes a call to SQL’s lower function:

SELECT email FROM users WHERE lower(email) = 'email@email.com';

However, today I found PostgreSQL’s citext module which provides a case-insensitive character string type. This means no more lower(email) in SQL statements 👍. It also means no need to update indexes to use lower(email) and transparent handling of UNIQUE indexes.

To enable citext execute the following statement in your psql prompt:

CREATE EXTENSION IF NOT EXISTS citext;

And then update the column type:

ALTER TABLE users ALTER COLUMN email TYPE citext;

This worked without issue on my system (PostgreSQL 9.5.2, Mac OS 10.11.4). If it fails for you, the most likely cause is that you don’t have the citext module available and will need to download it. For example, if you’re on Ubuntu you may need to execute:

$ sudo apt-get install postgresql-contrib

Checkout the PostgreSQL citext documentation for more info.