Postgres Syntax Upsert Merge

postgres-syntax-upsert-merge

Quick Reference :

PostgreSQL has two idempotent-write primitives :

1. INSERT ... ON CONFLICT (UPSERT, v9.5+) : single conflict target, single action (DO NOTHING or DO UPDATE). Fast, simple, the default.
2. MERGE (v15+) : multi-action sync against a source relation. Multiple WHEN MATCHED and WHEN NOT MATCHED branches, each with INSERT / UPDATE / DELETE / DO NOTHING. v17 adds RETURNING and WHEN NOT MATCHED BY SOURCE.

Pick UPSERT for "insert this row, but if it conflicts, update it". Pick MERGE for "take this batch and conditionally INSERT / UPDATE / DELETE depending on per-row state".

Two-line minimum-viable UPSERT :

INSERT INTO users (id, email, name) VALUES (1, 'a@b.c', 'Alice')
ON CONFLICT (id) DO UPDATE SET email = EXCLUDED.email, name = EXCLUDED.name;

Two-line minimum-viable MERGE (v15+) :

MERGE INTO users t USING new_users s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET email = s.email
WHEN NOT MATCHED THEN INSERT (id, email) VALUES (s.id, s.email);

When To Use This Skill :

ALWAYS use this skill when :

• A user asks "how do I do an upsert in PostgreSQL"
• Translating MySQL INSERT ... ON DUPLICATE KEY UPDATE or SQL Server MERGE
• Writing data-sync code (ETL, snapshot reconciliation, change-data-capture replay)
• Debugging ERROR 23505 unique_violation from an INSERT that "should have been an upsert"
• Debugging ERROR MERGE command cannot affect row a second time (cardinality violation)
• Choosing between UPSERT and MERGE for a new data-load pipeline
• Targeting v17 specifically and wanting RETURNING merge_action() for audit/event-emit patterns

NEVER use this skill for :

• UPDATE ... FROM (a different DML form, not idempotent insert)
• INSERT ... SELECT without conflict handling (plain insert, no upsert semantics)
• Bulk-load via COPY (see postgres-impl-bulk-loading)
• Foreign-data-wrapper writes (FDW + MERGE has v17 restrictions, document separately)

Decision Trees :

UPSERT or MERGE :

Is the operation "insert row, update on conflict" with a single conflict target ?
├── YES → INSERT ... ON CONFLICT (col) DO UPDATE.
└── NO  → Does the operation need multiple actions per row (some INSERT, some UPDATE, some DELETE) ?
         ├── YES → MERGE (v15+).
         └── NO  → Does the source-vs-target reconciliation need DELETE for rows missing from source ?
                  ├── YES → MERGE WITH WHEN NOT MATCHED BY SOURCE THEN DELETE (v17+).
                  └── NO  → INSERT ... ON CONFLICT is simpler.

Which conflict target :

Does the table have a single UNIQUE or PRIMARY KEY constraint that defines the conflict ?
├── YES → ON CONFLICT (col1, col2) - column list (planner picks the matching index).
└── NO  → Is there a named constraint you want to target explicitly ?
         ├── YES → ON CONFLICT ON CONSTRAINT constraint_name.
         └── NO  → Is the uniqueness conditional (partial unique index) ?
                  └── YES → ON CONFLICT (col) WHERE predicate — predicate must match the index WHERE clause exactly.

DO NOTHING or DO UPDATE :

Should an existing row be modified by the incoming values ?
├── NO  → DO NOTHING (idempotent insert, skip duplicates).
└── YES → DO UPDATE SET col = EXCLUDED.col [, ...] [WHERE condition].
         Use EXCLUDED.* to reference the proposed-but-rejected values.
         Use the target alias / table name to reference the existing row.

Core Syntax :

INSERT ... ON CONFLICT :

INSERT INTO target [(col1, col2, ...)] VALUES (...)
ON CONFLICT { (col1, col2, ...) [WHERE pred] | ON CONSTRAINT name }
  DO { NOTHING | UPDATE SET col = expr [, ...] [WHERE condition] }
[RETURNING expr [, ...]];

Rules :

• Conflict target MUST match an existing UNIQUE or PRIMARY KEY (NOT DEFERRABLE) index. Column list = arbiter index lookup. Constraint name = explicit binding.
• For partial unique indexes, the WHERE pred after the conflict target MUST be a textual subset of the index's WHERE clause. PostgreSQL matches the predicate, not the index name.
• EXCLUDED is a pseudo-table containing the row proposed for INSERT. Reference it as EXCLUDED.col.
• The existing-row side is referenced by the target table name : SET name = target_table.name || EXCLUDED.suffix.
• RETURNING returns the actually-inserted-or-updated rows (skips DO NOTHING no-ops).
• ON CONFLICT can NOT use exclusion constraints as arbiters. Only UNIQUE/PK indexes qualify.

MERGE (v15+) :

MERGE INTO target_table [ [AS] alias ]
USING { source_table | (subquery) } [ [AS] alias ]
ON join_condition
[ WHEN MATCHED [AND extra_condition] THEN
    { UPDATE SET col = expr [, ...] | DELETE | DO NOTHING } ]
[ WHEN NOT MATCHED [AND extra_condition] THEN
    { INSERT [(cols)] VALUES (exprs) | DO NOTHING } ]
[ WHEN NOT MATCHED BY SOURCE [AND extra_condition] THEN              -- v17+
    { UPDATE SET col = expr [, ...] | DELETE | DO NOTHING } ]
[ RETURNING merge_action(), expr [, ...] ];                          -- v17+

Rules :

• WHEN clauses evaluate in order; first match wins per source row. Order them most-specific first.
• The ON join condition must produce AT MOST ONE source row per target row. Otherwise : ERROR MERGE command cannot affect row a second time (SQLSTATE 21000, cardinality violation).
• WHEN NOT MATCHED (without BY SOURCE) is WHEN NOT MATCHED BY TARGET. The "BY TARGET" wording was added in v17 but the semantics are unchanged from v15.
• WHEN NOT MATCHED BY SOURCE is v17+ : matches target rows with no corresponding source row. Enables full-sync DELETE.
• MERGE cannot target materialized views or foreign tables (v15-v17). MERGE on updatable views with RULES is v17+.
• merge_action() returns 'INSERT' / 'UPDATE' / 'DELETE' per RETURNING row (v17+).

merge_action() Function :

Only valid inside a MERGE ... RETURNING clause (v17+). Returns the action that produced the row :

MERGE INTO users t USING new_users s ON t.id = s.id
WHEN MATCHED THEN UPDATE SET email = s.email
WHEN NOT MATCHED THEN INSERT (id, email) VALUES (s.id, s.email)
RETURNING merge_action() AS action, t.id, t.email;
-- action | id | email
-- UPDATE | 1  | a@b.c
-- INSERT | 2  | c@d.e

Common Errors :

Anti-Patterns :

The four most-common bugs in upsert/merge code (full list with reproducers in references/anti-patterns.md) :

1. ON CONFLICT without matching unique index → 42P10. The conflict target requires a real UNIQUE/PK constraint, not "intent".
2. MERGE with non-deterministic source → 21000 cardinality violation. Deduplicate the source before MERGE.
3. Using MERGE for simple upsert when INSERT ... ON CONFLICT would do. MERGE is 2-3x more verbose and harder to reason about for the single-conflict-target case.
4. EXCLUDED reference mistake : SET col = col (no-op against itself) vs SET col = EXCLUDED.col (apply proposed value). Easy to typo, silent semantic bug.

Version Notes :

If targeting a mixed-version fleet, write INSERT ... ON CONFLICT (works everywhere) and only reach for MERGE when the multi-action requirement is real. For RETURNING-from-merge on a v15/v16 fleet, fall back to UPSERT or to a CTE pattern with INSERT/UPDATE separately. See postgres-core-version-matrix for the full version table.

Reference Links :

• methods.md : full INSERT ... ON CONFLICT and MERGE syntax, conflict-target arbiter rules, EXCLUDED pseudo-table reference, WHEN-clause ordering.
• examples.md : 10 complete patterns including idempotent insert, counter increment, batch sync, full-sync MERGE, RETURNING audit log, partial-unique-index UPSERT.
• anti-patterns.md : 10 documented bugs with reproducible failure SQL and the official fix.

Official PostgreSQL documentation (verified 2026-05-19) :

• INSERT (ON CONFLICT) : https://www.postgresql.org/docs/17/sql-insert.html
• MERGE : https://www.postgresql.org/docs/17/sql-merge.html
• v17 Release Notes (MERGE RETURNING, WHEN NOT MATCHED BY SOURCE) : https://www.postgresql.org/docs/release/17.0/
• v15 Release Notes (MERGE introduced) : https://www.postgresql.org/docs/release/15.0/