PSQL: Storing Emails With citext

less than 1 minute read

Email addresses should be treated as case-insensitive because they are. If a user is trying to sign in with their email address, we shouldn’t care if they type or Both of those email addresses should be treated as equal and ultimately lead us to the same User record.

With the citext extension, we can create a column that acts as a case-insensitive text type. Any comparisons on a column of that type will internally have the lower function executed on the arguments.

The following example shows this in action:

create extension if not exists citext;

create table citext_emails (
  id serial primary key,
  email citext not null unique

insert into citext_emails (email) values ('');

select * from citext_emails where email = '';
--  id |      email
-- ----+------------------
--   1 |

Via jbranchaud/til.