Case insensitive UNIQUE constraints in Postgres

Adding UNIQUE constraints to tables in Postgres is very easy!

Imagine we have the following table:

CREATE TABLE users (  
  id     uuid PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),
  email  text
);

If we want to ensure that each user has a unique email we simply add:

ALTER TABLE users ADD CONSTRAINT email_unique UNIQUE (email);  

Let's try it out by inserting some data:

INSERT INTO users (email) VALUES ('test@example.com');  
INSERT INTO users (email) VALUES ('test@example.com');

ERROR:  duplicate key value violates unique constraint "email_unique"  
DETAIL:  Key (email)=(test@example.com) already exists.  

But there's a problem, the UNIQUE constraint is case sensitive!

INSERT INTO users (email) VALUES ('test@example.com');  
INSERT INTO users (email) VALUES ('TEST@example.com');

SELECT * from users;

                  id                  |      email       
--------------------------------------+------------------
 ccfcddd2-bdc5-4cf4-9475-4171960e6262 | test@example.com
 431308b4-8df8-44c9-bed4-7c44cf4e1ec1 | TEST@example.com
(2 rows)

Unfortunately, Postgres does now allow us to define a unique constraint using LOWER like:

ALTER TABLE users ADD CONSTRAINT email_unique UNIQUE (LOWER(email));

ERROR:  syntax error at or near "("  

But don't worry there's a workaround! Instead of using the text data type, we can use the citext (case insensitive text) type! First we need to enable the citext extension:

CREATE EXTENSION IF NOT EXISTS citext;  

Then we'll need to change the email data type in the users table:

ALTER TABLE users ALTER COLUMN email TYPE citext;  

Now our existing UNIQUE constraint should be case insensitive!

INSERT INTO users (email) VALUES ('test@example.com');  
INSERT INTO users (email) VALUES ('TEST@example.com');

ERROR:  duplicate key value violates unique constraint "email_unique"  
DETAIL:  Key (email)=(TEST@example.com) already exists.  

Another solution is to add a UNIQUE INDEX instead of a CONSTRAINT like:

CREATE UNIQUE INDEX email_unique_idx on users (LOWER(email));