How to Prevent Duplicate NULL Values with PostgreSQL UNIQUE NULLS NOT DISTINCT

A PostgreSQL column that allows missing values is nullable. A unique constraint on that column normally allows multiple rows with NULL. If your data rules allow only one row for each key, including when a key value is missing, PostgreSQL 15 and later provide UNIQUE NULLS NOT DISTINCT. This option tells PostgreSQL to treat NULL values as equal when it checks for duplicates.

A NULL means a value is missing or unknown. By default, PostgreSQL does not count two NULL values as duplicates. Add NULLS NOT DISTINCT to a unique constraint or index to reject a row when all its key values match another row, including when both rows have NULL in the same key columns.

Add NULLS NOT DISTINCT to the unique key

For example, suppose each tenant can have one row for a given external reference, and each tenant can have only one row with no external reference. Define the constraint like this:

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

The constraint allows the same external_ref for different tenants. For a given tenant, however, PostgreSQL rejects a second row with the same reference or with external_ref set to NULL. The uniqueness check applies to the combination of both columns.

You can add the rule to an existing table with a named constraint:

ALTER TABLE account_alias
ADD CONSTRAINT account_alias_tenant_ref_key
UNIQUE NULLS NOT DISTINCT (tenant_id, external_ref);

PostgreSQL can also enforce the rule with a unique index, an index that rejects duplicate keys:

CREATE UNIQUE INDEX account_alias_tenant_ref_idx
ON account_alias (tenant_id, external_ref)
NULLS NOT DISTINCT;

Use a constraint when the rule belongs in the table’s declared data rules. Use an index when you need uniqueness without a named table constraint, such as when the index applies only to rows that meet a WHERE condition. The PostgreSQL syntax discussion shows both forms.

Why the default allows duplicate NULLs

By default, PostgreSQL treats NULL values as distinct for unique checks. SQL comparisons involving NULL, including NULL = NULL, do not evaluate to true. As a result, a regular unique constraint allows several rows with NULL in a constrained column. The PostgreSQL uniqueness discussion describes this default behavior.

NULLS NOT DISTINCT changes how PostgreSQL checks each key column in that constraint or index. It does not make every NULL in the table unique. When a key uses more than one column, PostgreSQL rejects a duplicate only when all the key values match, including columns where both rows contain NULL. For example, (tenant_id = 1, external_ref = NULL) and (tenant_id = 2, external_ref = NULL) remain distinct keys.

PostgreSQL applies the option to all key columns in the same index. If one nullable column should allow repeated NULL values while another should treat them as equal, put those rules in separate constraints or indexes.

Check the PostgreSQL version and existing rows

NULLS NOT DISTINCT requires PostgreSQL 15 or later; PostgreSQL 14 and earlier do not support the syntax. Check which PostgreSQL version the server runs before changing the schema:

SHOW server_version;

The feature was introduced in PostgreSQL 15, as noted in this discussion of the version change.

Before adding the constraint, find rows that would violate it. A GROUP BY puts rows with matching values in the same group, including rows whose grouped columns contain NULL:

SELECT tenant_id, external_ref, count(*)
FROM account_alias
GROUP BY tenant_id, external_ref
HAVING count(*) > 1;

Resolve any duplicate groups before adding the constraint. PostgreSQL will reject the constraint or unique index if existing rows violate the new rule.

If you use PostgreSQL 14 or earlier, consider whether a partial unique index, which applies only to rows that meet a condition, fits the rule. This approach is especially useful for a single nullable column. For multiple nullable columns, partial-index workarounds become more complex. A sentinel-value approach, which uses a reserved value in place of NULL, requires a value that real data can never use. These trade-offs are covered in the discussion of unique constraints with nullable columns.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com