2026-09-22 · 6 min read · Rahul Chitturi
- sql
- postgresql
- data-integrity
Ordinary PostgreSQL uniqueness treats null values as distinct, so several rows can share a null in an otherwise unique column. NULLS NOT DISTINCT changes that comparison and can enforce at most one row for the same nullable key.
Surprises appear when development and production schemas differ, a composite key contains several nullable columns, or an ORM assumes the traditional behavior.
Inspect the actual index definition
Read the deployed constraint and index metadata rather than inferring behavior from a model declaration. Record all key expressions, included columns, predicates, collations, operator classes, and the null-distinctness setting.
- Distinguish a unique constraint from a partial unique index.
- Check every nullable member of a composite key.
- Treat database enforcement as authoritative.
- Classify unique violations without parsing message text.
Plan migration collisions
Before changing an existing index, group rows under the proposed equality rule and resolve duplicate null-bearing keys. The replacement must use a deployment path supported by the PostgreSQL version and cannot leave concurrent writes temporarily unenforced.
Coordinate old and new application versions so neither relies on behavior unavailable during the transition.
Exercise boundary cases
Use Flashman's SQL formatter for DDL and diagnostic queries, diff for schema snapshots, JSON formatter for synthetic records, and case converter for identifier conventions.
Test all-null and partially null composites, non-null duplicates, collations, concurrent inserts, updates to and from null, UPSERT target inference, bulk loads, replicas, rollback, and ORM schema generation.