Postgres NULL uniqueness: define the missing-key rule

A PostgreSQL unique constraint normally permits repeated keys when a constrained column is NULL. Use NULLS NOT DISTINCT when an absent value must occupy one unique slot, or keep the default behavior when NULL represents independently unknown values. Decide that meaning before changing the schema, then reconcile existing duplicates and verify concurrent writes against the chosen rule. The option has been available since PostgreSQL 15. ^{[1] [6]}

Scenario: a notification service stores message templates by tenant, template key and optional locale. A NULL locale means the tenant's default template. Named locales override that default. The table already has an ordinary composite unique constraint, but repeated imports have created competing defaults. Each import passed validation because the database's uniqueness rule differed from the product's identity rule.

The schema clause leaves a product decision unresolved. Choose the approved default, define omitted locale in the API, and use that identity for both reads and writes. This article uses PostgreSQL 18 documentation for the current syntax and explicitly distinguishes illustrative SQL from executed database tests. ^[1]

Does NULL mean unknown or tenant default?#

For this service, NULL is a product sentinel: the one default for a tenant and template key. That is a legitimate design choice, but it is different from an unknown locale on an unfinished import. If both meanings share the same representation, the uniqueness decision is underspecified before any SQL is written.

An unknown imported record might need to coexist with other unknown records until classification completes. A tenant default needs one addressable identity. Putting those records in separate workflow states or tables can be clearer than asking a uniqueness rule to infer intent. Do not use NULLS NOT DISTINCT simply because duplicate rows look untidy.

PostgreSQL's default comparison for uniqueness treats null values as distinct. In a composite unique key, nullability can therefore permit repeated combinations that an application regards as the same entity. The documentation makes this behavior explicit; it is not an intermittent failure of the index. ^{[1] [2]}

Also separate NULL from empty text. In the scenario, an empty locale string is rejected or normalized at the API boundary according to an explicit policy. Treating both as default in application code while storing them separately would create another identity mismatch that changing NULL handling alone cannot fix.

Write the allowed-row matrix first#

Write the allowed row combinations before approving the migration. For the template service, each tenant can have one default for a given template key, while different named locales and different tenants remain independent.

This is the scenario's product rule, not a universal model for nullable keys. Add rows for the cases your service actually permits: archived templates, draft revisions, fallback chains and normalization. If archived records can repeat the active identity, the proposed constraint over every row is too broad.

For a rule that applies only to a subset of rows, PostgreSQL supports unique partial indexes. That choice changes the enforcement object and may change how an upsert identifies its arbiter. Do not describe a partial index as an ordinary table-level unique constraint or assume it can be attached as one. ^{[1] [4] [5]}

How does the database enforce that rule?#

On PostgreSQL versions supporting the option, a unique constraint can treat nulls as equal for uniqueness purposes. The full composite key still matters: a NULL locale does not create one global default across every tenant. PostgreSQL's unique-index documentation describes comparison across all indexed columns. ^{[1] [2]}

The following isolated table definition illustrates the new contract. It uses a separate demo name to make the learning example distinct from production migration commands.

CREATE TEMP TABLE message_template_demo (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id bigint NOT NULL,
    template_key text NOT NULL,
    locale text,
    body text NOT NULL,
    CONSTRAINT template_demo_identity
        UNIQUE NULLS NOT DISTINCT
        (tenant_id, template_key, locale)
);

INSERT INTO message_template_demo
    (tenant_id, template_key, locale, body)
VALUES
    (101, 'payment_due', NULL, 'Default message'),
    (101, 'payment_due', 'nl-NL', 'Dutch message'),
    (202, 'payment_due', NULL, 'Other tenant default');

Scenario: adding another default for tenant A and the same key should now fail, while adding a French override should succeed. Updating an existing named override to NULL should also fail when that default is already occupied. Test updates as well as inserts; identity collisions do not occur only at initial creation.

The constraint does not make locale required. NULL remains a stored value, with different treatment during the uniqueness check. A primary key or explicit NOT NULL constraint expresses a separate requirement. Keeping tenant and template key non-null avoids accidentally introducing more missing-key meanings into the same identity. ^[1]

Avoid replacing NULL with an arbitrary string merely to obtain a familiar unique constraint. A sentinel string can collide with real input, leak into external APIs, or become inconsistent with older readers. An explicit non-null default identifier can be a good schema design, but it deserves its own migration and compatibility analysis.

What must happen to existing duplicates?#

Inventory duplicate groups under the proposed identity before building enforcement. Grouping by the same key columns provides a review queue. The query below identifies groups; it intentionally does not pick a survivor or delete data.

SELECT tenant_id, template_key, locale,
       count(*) AS row_count,
       array_agg(id ORDER BY id) AS conflicting_ids
FROM message_template
GROUP BY tenant_id, template_key, locale
HAVING count(*) > 1;

The product owner might choose the latest approved template, preserve a manually reviewed version, or merge conflicting content. An identifier or timestamp alone is not evidence of approval. Record the chosen row, conflicting bodies, related references and reason for the decision. Preserve enough history to explain a later complaint about changed notification text.

After cleanup, prevent writers from recreating the invalid state during the enforcement transition. The safe approach depends on table size, write volume and availability requirements. A small maintenance window can be easier to reason about than a complex online transition. A large active table may justify concurrent index creation with a carefully reviewed cutover. ^{[3] [4]}

Here is the shape of the latter option for an ordinary, non-partitioned table. It is a migration outline, not a script to paste into a running service. Review the real constraint names, dependencies, permissions, lock budget and rollback plan first.

-- Run outside a transaction block.
CREATE UNIQUE INDEX CONCURRENTLY template_identity_new_idx
ON message_template (tenant_id, template_key, locale)
NULLS NOT DISTINCT;

-- Separate controlled cutover after successful index verification.
ALTER TABLE message_template
ADD CONSTRAINT template_identity_key
UNIQUE USING INDEX template_identity_new_idx;

CREATE INDEX CONCURRENTLY cannot run inside a transaction block. It can fail and leave an invalid index requiring deliberate recovery; inspect the documented state before retrying under the same name. The cutover still involves table-level coordination. Concurrent construction is not a promise that the entire migration has no locking consequences. ^{[3] [4]}

Keep the old constraint until the new enforcement and application behavior are verified, then review its dependencies before removing it. Two enforcing indexes can impose additional write cost during the transition. The old rule is not a rollback for data already normalized or merged; schema rollback and data restoration require different records.

For the operational branches, use the existing guides to invalid concurrent indexes and ALTER TABLE lock queues. This article's distinct decision is what the key means, and how that meaning survives the transition.

Can the application still race or upsert incorrectly?#

Yes. An application-side existence check remains vulnerable to another writer between the check and insert. Database enforcement resolves the collision, but the application must interpret the outcome correctly. A duplicate-default error should not become a generic retry loop that repeatedly submits the same forbidden identity.

For a write API whose explicit behavior is replace this template, an upsert can target the new named constraint. The example assigns the body from the proposed row. It does not merge translations, enforce an approval workflow or decide which concurrent editor's business intent should win. ^[5]

INSERT INTO message_template
    (tenant_id, template_key, locale, body)
VALUES
    ($1, $2, $3, $4)
ON CONFLICT ON CONSTRAINT template_identity_key
DO UPDATE SET body = EXCLUDED.body
RETURNING id, tenant_id, template_key, locale;

PostgreSQL documents the atomic insert-or-update outcome for ON CONFLICT DO UPDATE, subject to independent errors. That does not establish a product-level ordering between concurrent editors. If stale content must not overwrite an approved revision, add an explicit revision or approval condition to the write contract and test its failure response. ^[5]

If the application infers an arbiter from columns rather than naming a constraint, review which matching unique indexes exist during the transition. The insert documentation explains index inference and partial predicates. A migration that changes indexes without reviewing the application's conflict target can leave the service enforcing one identity and selecting another write path. ^[5]

Read paths need the same semantics. A query that requests the default should deliberately select NULL locale, while locale fallback should have an explicit priority. A new uniqueness rule ensures at most one matching default; it does not automatically make a fallback query choose that default correctly.

How do you verify the changed contract?#

Rehearse the allowed-row matrix against the intended PostgreSQL version. Check insert, update and upsert behavior, then repeat the conflicting-default case with concurrent sessions. Record whether the database rejects a duplicate or performs the intended replacement, and verify that another tenant's default remains untouched.

For a migration rehearsal, begin with deliberately conflicting historical rows and require an explicit survivor record before enforcement. Attempt a new conflicting write during the transition. Confirm the migration either blocks that write through the chosen coordination mechanism or fails visibly, rather than declaring success while duplicates remain.

Inspect the final constraint and index definitions in the catalog. Confirm validity, column order and null treatment instead of trusting the migration filename. Run the application's default-selection and localization tests after the schema check. These tests establish different things and should keep separate results.

The example here is grounded in PostgreSQL documentation and a synthetic model of the intended key matrix. No live PostgreSQL migration, database concurrency run or query benchmark was performed for this article. Treat the SQL as a reviewed rehearsal starting point, with database execution still required in your own acceptance environment.

Why does UNIQUE allow multiple NULL values?

PostgreSQL's default uniqueness behavior treats null values as distinct. A nullable column can therefore permit repeated apparent keys, including composite keys with an absent component. Use the explicit null-treatment option only when it matches the intended data model. ^{[1] [2]}

Does NULLS NOT DISTINCT allow only one NULL in the table?

It enforces the complete unique key. In a composite tenant-scoped constraint, different tenants or different template keys can still each have a NULL locale. Only repeated combinations under the full comparison are rejected. ^[2]

Can I add the rule without resolving existing duplicates?

A unique index build must establish uniqueness under its comparison rule. Existing conflicts or concurrent conflicting writes can prevent that. Audit the proposed identity, select survivors deliberately, coordinate writers and inspect the resulting index state before completing the migration. ^[3]

Should an unknown value always use NULLS NOT DISTINCT?

No. Independent unknown values may need to coexist. A single default slot is a different product concept. Define which meaning the column carries, and consider separate workflow state or identity when one nullable field is serving both purposes. ^[1]