Using PostgreSQL's Case-Insensitive Text (citext) Module
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:
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:
And then update the column type:
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.