You cannot apply a unique constraint to an expression over a column, e.g.
lower(email). You can, however, create a generated
column for
that expression and then apply the unique constraint to that generated column.
Here is what that could look like:
> create table users (
id integer generated always as identity primary key,
name text not null,
email text not null,
email_lower text generated always as (lower(email)) stored,
unique ( email_lower )
);
> \d users
+-------------+---------+-----------------------------------------------------------------+
| Column | Type | Modifiers |
|-------------+---------+-----------------------------------------------------------------|
| id | integer | not null generated always as identity |
| name | text | not null |
| email | text | not null |
| email_lower | text | default lower(email) generated always as (lower(email)) stored |
+-------------+---------+-----------------------------------------------------------------+
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"users_email_lower_key" UNIQUE CONSTRAINT, btree (email_lower)And then an demonstration of violating that constraint:
> insert into users (name, email) values ('Bob', '[email protected]');
INSERT 0 1
> insert into users (name, email) values ('Bobby', '[email protected]');
duplicate key value violates unique constraint "users_email_lower_key"
DETAIL: Key (email_lower)=(bob@email.com) already exists.The main tradeoff here is that you are doubling the amount of storage you need for that column. Unless it is a massive table, that is likely not an issue.