Postgres 15 gave us the ability to specify how we want null values to be treated when dealing with unique indexes.
By default, nulls are considered unique values in Postgres:
create table users (name text, email text unique);
-- CREATE TABLE
insert into users values ('Joe', null), ('Jane', null);
-- INSERT 0 2
This default behavior can also be explicitly set using the nulls distinct
clause:
create table users (name text, email text unique nulls distinct);
-- CREATE TABLE
insert into users values ('Joe', null), ('Jane', null);
-- INSERT 0 2
To change the default behavior and prevent nulls from being considered unique values, you can use the nulls not distinct
clause:
create table users (name text, email text unique nulls not distinct);
-- CREATE TABLE
insert into users values ('Joe', null), ('Jane', null);
-- ERROR: duplicate key value violates unique constraint "users_email_key"
-- DETAIL: Key (email)=(null) already exists.
See this change in the Postgres 15 release notes