PostgreSQL Null-Aware Unique Constraints

A PostgreSQL unique constraint normally permits more than one null value. That behavior follows from the default treatment of nulls as distinct for uniqueness checks, and it can leave a gap when a nullable column still represents a business key.

NULLS NOT DISTINCT changes that specific part of uniqueness semantics. Null values compare as equivalent for the constraint, so a second row with the same null-bearing key is rejected.

Default uniqueness permits repeated nulls

Consider a table that stores one external identifier per account, with the identifier optional during an initial state:

CREATE TABLE account_links (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    provider text NOT NULL,
    external_id text,
    active boolean NOT NULL DEFAULT true,
    UNIQUE (provider, external_id)
);

The unique constraint rejects two rows with the same non-null pair:

INSERT INTO account_links (provider, external_id)
VALUES ('acme', 'A-42');

INSERT INTO account_links (provider, external_id)
VALUES ('acme', 'A-42');
-- duplicate key error

A null changes the result. Under the default semantics, these rows can coexist:

INSERT INTO account_links (provider, external_id)
VALUES ('acme', NULL);

INSERT INTO account_links (provider, external_id)
VALUES ('acme', NULL);

The constraint is still active. PostgreSQL simply does not consider those null values equal for the uniqueness comparison.

That distinction matters when the intended rule is “at most one row for this provider when the external identifier is absent.” A conventional unique constraint does not express that rule.

NULLS NOT DISTINCT closes the nullable-key gap

PostgreSQL can make null participate in uniqueness as a single equivalent value:

CREATE TABLE account_links (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    provider text NOT NULL,
    external_id text,
    active boolean NOT NULL DEFAULT true,
    UNIQUE NULLS NOT DISTINCT (provider, external_id)
);

The first ('acme', NULL) row is valid. A second row with that same pair conflicts with the constraint.

The behavior also applies when several columns are nullable. A multicolumn unique constraint rejects a new row when every constrained position compares equal under its uniqueness semantics, including null positions covered by NULLS NOT DISTINCT.

For example:

CREATE TABLE routing_rules (
    region text,
    tenant_id bigint,
    route text NOT NULL,
    UNIQUE NULLS NOT DISTINCT (region, tenant_id, route)
);

Two rows containing (NULL, NULL, '/status') conflict. So do two rows containing ('eu', NULL, '/status'). A row containing ('us', NULL, '/status') remains distinct because region differs.

This is narrower than making the columns NOT NULL. The columns remain nullable; the constraint only changes how null values participate in duplicate detection.

The index form has the same null policy

A unique constraint creates a unique B-tree index to enforce the rule. PostgreSQL also exposes the null policy directly in CREATE UNIQUE INDEX:

CREATE UNIQUE INDEX account_links_provider_external_idx
ON account_links (provider, external_id)
NULLS NOT DISTINCT;

For a schema-level data rule, a unique constraint usually states the intent more directly. A standalone unique index is useful when index-specific features are required, such as an expression or a predicate.

The distinction becomes relevant for conditional rules. Suppose null external identifiers need to be unique only for active rows. A partial unique index can scope enforcement:

CREATE UNIQUE INDEX active_account_links_key_idx
ON account_links (provider, external_id)
NULLS NOT DISTINCT
WHERE active;

Only rows satisfying the predicate participate in that index. NULLS NOT DISTINCT controls null comparison among those indexed rows; the predicate controls which rows enter the uniqueness set.

Existing data must already satisfy the rule

Changing from default null treatment to NULLS NOT DISTINCT can expose duplicates that were previously valid. A table containing several rows with the same null-bearing key cannot receive the stricter constraint until those rows are resolved.

A diagnostic query can group the candidate key using SQL grouping semantics:

SELECT provider, external_id, count(*)
FROM account_links
GROUP BY provider, external_id
HAVING count(*) > 1;

This reports duplicate groups, including groups where external_id is null. The result set should be interpreted against the exact columns and predicate planned for the new constraint or index.

Migration design also needs to account for concurrent writes. Checking for duplicates in one statement and adding enforcement later leaves an interval in which another transaction can insert a conflicting row. The appropriate migration sequence depends on table size, write traffic, lock tolerance, and the PostgreSQL release in use.

Null equality here is local to uniqueness

NULLS NOT DISTINCT does not redefine SQL null comparison across the database. Expressions such as NULL = NULL still evaluate to unknown rather than true. Joins, filters, and other expressions keep their normal null semantics.

The clause affects duplicate detection performed by the unique constraint or unique index. Code that needs null-safe comparison in a query can use operators intended for that purpose, such as IS NOT DISTINCT FROM:

SELECT *
FROM account_links
WHERE external_id IS NOT DISTINCT FROM NULL;

Keeping these concepts separate avoids a common modeling error. Constraint equality determines which stored key combinations may coexist. Query comparison determines how expressions evaluate while reading or modifying rows.

Portability needs an explicit decision

Null treatment in unique constraints is not identical across database systems. PostgreSQL supports both its default distinct-null behavior and the explicit NULLS NOT DISTINCT form, but a schema intended for multiple database engines cannot assume matching syntax or semantics.

That makes nullable uniqueness a data-model decision rather than a cosmetic index option. If null represents a real state in a key, the allowed cardinality of that state should be stated deliberately.

NULLS NOT DISTINCT is a compact fit when the rule is global: null is permitted, but repeated null-bearing keys are not. Partial indexes and NOT NULL constraints express different rules. Choosing among them starts with the set of row combinations the schema is meant to admit, then maps that set to the database mechanism that enforces it directly.