Storing Emails With citext

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 [e-mail] or [e-mail]. 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 ('[e-mail]');

select * from citext_emails where email = '[e-mail]';
--  id |      email
-- ----+------------------
--   1 | [e-mail]

See citext-emails.sql for a full example.