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
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.
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.