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 [email protected] or [email protected]. 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 ('[email protected]');
select * from citext_emails where email = '[email protected]';
-- id | email
-- ----+------------------
-- 1 | [email protected]See
citext-emails.sql
for a full example.