2026-09-26 · 8 min read
- sql
- postgresql
- concurrency
PostgreSQL MERGE joins a source relation to a target, classifies candidate change rows, and executes the first applicable WHEN action. It can consolidate conditional insert, update, delete, and no-op logic into one statement.
MERGE is not a substitute for keys, constraints, or a concurrency design. Source duplication, target ambiguity, changing snapshots, triggers, and row-level security still determine whether the result preserves the business invariant.
Define a deterministic source
State the business key and prove that each source batch has at most one authoritative row for it. If deduplication is required, define a stable winner with explicit ordering and preserve rejected duplicates for diagnosis rather than letting query-plan order choose.
- Enforce target identity with an appropriate constraint.
- Validate source uniqueness before mutation.
- Keep null-key semantics explicit.
- Bound the source batch and transaction size.
Separate matching from action filters
Use the join condition to express source-to-target identity. Put action eligibility in ordered WHEN clauses and document why the first applicable clause wins. Hiding target filters in the join can classify an existing row as not matched and lead to an attempted duplicate insert.
Review version-specific syntax and behavior. PostgreSQL capabilities evolve, and examples written for another database product may attach different semantics to similarly named MERGE forms.
Preserve invariants under concurrency
List the transactions that can insert, update, or delete the same keys. Choose isolation, explicit locking, constraints, and retry behavior based on the invariant rather than expecting one statement to eliminate races.
Handle unique violations, deadlocks, and serialization failures as distinct outcomes. Retry the enclosing transaction only when every database and external effect is idempotent or coordinated through a durable outbox.
Inventory triggers, policies, and privileges
Trace row and statement triggers for every possible action, including generated columns, partition routing, foreign keys, and downstream audit writes. Verify role privileges and row-level security with the actual deployment identity.
Do not rely on a generic command tag as a detailed action report. If the application needs per-action counts or changed identifiers, design an explicit, tested observation mechanism compatible with the deployed PostgreSQL version.
Rehearse the transaction matrix
Use Flashman's SQL formatter for source, MERGE, and diagnostic queries, JSON formatter for synthetic batches, diff for expected table snapshots, timestamp converter for transaction timelines, and cron helper for controlled batch schedules.
Cover no match, one match, multiple clauses, duplicate source rows, null keys, concurrent insert and delete, constraints, triggers, partitions, RLS, permission failures, deadlocks, serialization retries, rollback, replicas, restores, and mixed application versions.