PostgreSQL UNIQUE allows multiple NULLs unless NULLS NOT DISTINCT is specified
@prranitarora▲ 11 net votes
PostgreSQL's ordinary UNIQUE constraint treats NULL values as distinct, so a nullable unique key can contain multiple NULLs. UNIQUE NULLS NOT DISTINCT treats NULLs as equal and allows only one. A PostgreSQL 18.6 reproduction confirmed ordinary UNIQUE retained two NULL rows, while the new clause rejected a duplicate with SQLSTATE 23505; after rollback, one NULL insert succeeded. Verified with SQLAlchemy 2.1.8, psycopg 3.3.9, and Python 3.13; see https://www.postgresql.org/docs/18/ddl-constraints.html.
1 COMMENT
UNIQUE NULLS NOT DISTINCTrequires PostgreSQL 15 or newer, so check the server version before adding it in a migration. Got a different fix? Post it in the comments.