A nullable column with a conventional PostgreSQL unique constraint can contain more than one NULL. The index still rejects repeated non-null values, but null entries are distinct for uniqueness checks by default. NULLS NOT DISTINCT changes that rule and makes null entries collide with each other.

This option is useful when NULL represents a single missing or unassigned state that must occur at most once within the constrained key. It changes uniqueness semantics without changing the column into NOT NULL.

Default uniqueness permits multiple null entries

A standard unique constraint rejects duplicate values:

CREATE TABLE device_slot (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    rack_code text UNIQUE
);

Two rows cannot both contain rack_code = 'R7'. Multiple rows can contain NULL, however, because PostgreSQL treats null values as distinct for a unique constraint unless configured otherwise.

That behavior is separate from ordinary equality. SQL null semantics do not make NULL = NULL evaluate to true. Unique enforcement has its own documented null treatment, and PostgreSQL exposes a clause that selects the desired behavior.

NULLS NOT DISTINCT changes duplicate detection

The constraint can state the alternate rule directly:

CREATE TABLE device_slot (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    rack_code text UNIQUE NULLS NOT DISTINCT
);

After one row with a null rack_code exists, another null value conflicts with the unique constraint. Repeated non-null values continue to conflict as before.

The same behavior is available in table-constraint form:

CREATE TABLE reservation_key (
    tenant_id bigint NOT NULL,
    external_ref text,
    UNIQUE NULLS NOT DISTINCT (tenant_id, external_ref)
);

For a multicolumn unique constraint, a duplicate is rejected when all constrained columns compare as the same key under the selected null treatment. With NULLS NOT DISTINCT, two rows such as (42, NULL) conflict. Rows (42, NULL) and (43, NULL) do not, because the tenant_id values differ.

Enforcement still uses a unique B-tree index

PostgreSQL creates a unique B-tree index to enforce a unique constraint. The index records the constrained key and applies the selected null-distinctness rule during uniqueness checks.

The equivalent index syntax can also be written explicitly:

CREATE UNIQUE INDEX reservation_key_idx
ON reservation_key (tenant_id, external_ref)
NULLS NOT DISTINCT;

Only B-tree indexes can be declared unique in PostgreSQL. A unique constraint normally provides the clearer schema-level declaration when the rule is an integrity constraint rather than an indexing detail.

NULLS NOT DISTINCT does not make a nullable column mandatory. A row may still contain NULL; the constraint limits duplicate null-containing keys. A primary key remains different because its columns are both unique and non-null.

Composite keys make the distinction more visible

Consider a table that stores one optional external identifier per account:

CREATE TABLE account_alias (
    account_id bigint NOT NULL,
    alias text,
    UNIQUE NULLS NOT DISTINCT (account_id, alias)
);

These rows can coexist:

account_id | alias
-----------+-------
10         | NULL
11         | NULL
10         | alpha
10         | beta

A second (10, NULL) row is rejected. A second (10, 'alpha') row is also rejected. The null rule therefore applies inside the complete composite key rather than imposing one global null across the table.

Without NULLS NOT DISTINCT, repeated (10, NULL) rows would be permitted by the unique constraint. That difference matters when a nullable component is intended to denote one specific state inside each surrounding key.

Portability needs an explicit decision

PostgreSQL documents the default treatment of nulls in unique constraints as implementation-defined by the SQL standard, and database systems can differ. Schema behavior that depends on nullable uniqueness should therefore be treated as a database-specific contract rather than inferred from the word UNIQUE alone.

NULLS NOT DISTINCT makes that contract visible in PostgreSQL DDL. It is most precise when the data model permits a null value but requires that null-containing keys obey the same duplicate boundary as concrete values.

PostgreSQL Database Constraints Indexes