2026-09-22 · 8 min read
- sql
- postgresql
- data-integrity
SQL null represents an unknown or absent value and ordinary PostgreSQL unique indexes allow multiple rows whose indexed values compare with null. NULLS NOT DISTINCT changes unique-index treatment so nulls participate as equal for uniqueness.
The feature is useful when a nullable business key should have only one missing-value row within its scope. It should follow an explicit data rule rather than being added merely because duplicate nulls look untidy.
Define identity for the complete key
Write examples of allowed and rejected rows before selecting the index. For a composite key, specify behavior when one, several, or all members are null and include tenant, lifecycle, or soft-delete scope where required.
- Distinguish missing values from empty strings and sentinel values.
- Define collation and case behavior for text members.
- Preserve a database constraint as the concurrency authority.
- Choose user-facing conflict behavior separately.
Choose a constraint or index shape
A unique constraint is appropriate for a whole-table key and can participate in supported constraint semantics. A partial or expression unique index can encode a narrower rule but has different metadata, foreign-key, migration, and UPSERT implications.
Inspect server-version support and generated DDL from migration tools. Do not assume an ORM's nullable unique annotation requests NULLS NOT DISTINCT, and do not emulate the rule with a race-prone pre-insert lookup.
Migrate without an enforcement gap
Scan existing data under the proposed key and resolve every collision before validation. Plan index creation, lock levels, attachment, application compatibility, and rollback using operations supported by the deployed PostgreSQL version.
During mixed-version rollout, ensure every writer understands the new conflicts. Categorize database error codes at the boundary and retry only complete operations that are safe to repeat.
Maintain a null-combination matrix
Use Flashman's SQL formatter for schema and diagnostic queries, diff for catalog output, JSON formatter for synthetic API values, case converter for identifiers, and UUID generator for disposable keys.
Test every null combination, non-null duplicates, text collations, expression and partial predicates, concurrent inserts, transitions to and from null, UPSERT inference, bulk imports, logical replication, dumps and restores, ORM introspection, migration rollback, and version upgrades.