2026-09-18 · 6 min read · Rahul Chitturi
- sql
- postgresql
- upsert
A PostgreSQL INSERT ... ON CONFLICT statement can fail to find a matching unique index even when a similar partial index exists. Conflict-target inference must identify a compatible unique arbiter, including the relevant columns or expressions and predicate.
This often appears after soft deletion or tenant scoping introduces uniqueness only for active rows. The write statement and schema then need to express the same invariant.
Compare the target with the actual index
Inspect the deployed table definition rather than a migration file alone. Record indexed columns or expressions, operator classes, collation, uniqueness, and the partial predicate before changing the upsert.
- Check for schema drift between environments.
- Match expression and predicate semantics intentionally.
- Include tenant keys in tenant-scoped uniqueness.
- Confirm the intended row is covered by the partial index.
Trace insert and update paths separately
The conflict action has its own privileges, expressions, conditions, triggers, and return values. A matching arbiter does not guarantee the update branch is authorized or changes the expected row.
One insert statement containing duplicate proposed keys can also affect the same existing row more than once. Deduplicate or reject the input batch under a documented policy.
Reproduce with a minimal schema
Use the SQL formatter for the table, index, and upsert, JSON formatter for synthetic batches, UUID generator for harmless tenant IDs, and diff for development-versus-production definitions.
Test active and soft-deleted rows, nulls, expression variants, predicate boundaries, two concurrent inserts, duplicate source rows, conditional updates, triggers, permissions, RETURNING values, and the deployed PostgreSQL version.