2026-09-18 · 8 min read
- sql
- postgresql
- upsert
PostgreSQL can use a unique index as the arbiter for INSERT ... ON CONFLICT. With a partial unique index, only rows satisfying its predicate participate in that uniqueness rule, which is useful for active records, scoped identifiers, and soft-deletion models.
The insert statement must identify a compatible arbiter. Similar-looking columns are insufficient when the index uses expressions, a predicate, collation, operator classes, or tenant keys that encode the actual invariant.
State the uniqueness invariant first
Write the business rule in plain language before creating the index or upsert. For example, active usernames may be unique within a tenant, while deleted historical rows may retain their old names.
- Include every scope key required by the invariant.
- Define how nulls participate in uniqueness.
- Choose normalization expressions intentionally.
- Make soft-delete and active-state predicates explicit.
Align conflict-target inference
A conflict target can name columns or expressions and can include an index predicate. Compare it against the deployed unique indexes and verify that PostgreSQL can infer the intended arbiter.
Naming a constraint can be appropriate for a table constraint, but partial uniqueness is represented by an index rather than an ordinary partial unique constraint. Avoid coupling application behavior to assumptions the schema cannot express.
Specify the update branch
EXCLUDED values come from the proposed row, while the target table supplies the conflicting row. Define exactly which columns may change, whether a conditional update can skip stale writes, and what RETURNING means for inserted, updated, or skipped rows.
Review insert, update, select, trigger, and row-security privileges separately. A correct conflict target can still fail or expose unintended data in the action branch.
Exercise schema and concurrency together
Use Flashman's SQL formatter for DDL and DML, JSON formatter for synthetic batches, UUID generator for test tenants, diff for deployed index definitions, and timestamp converter for concurrency traces.
Test active and deleted rows, nulls, expression normalization, predicate boundaries, cross-tenant values, two simultaneous inserts, duplicate source rows, conditional updates, triggers, row security, migration overlap, schema drift, RETURNING, and the exact PostgreSQL version in production.