Specify behavior for nulls in a unique index

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