Postgres version15+CategorySQL / DMLComplexityIntermediate
SQL has had a MERGE statement in its standard since 1999. Postgres shipped it in version 15, released in October 2022. That is twenty-three years. In the meantime, developers used INSERT ... ON CONFLICT DO UPDATE, a Postgres-specific extension that works well but does not compose the same way. Now that MERGE is here, it is worth understanding what it actually does and when to reach for it.
What MERGE Does
MERGE operates on a target table and a source. For each row in the source, it checks whether a matching row exists in the target, then routes to the appropriate action. The basic form looks like this:
MERGE INTO orders AS target
USING new_orders AS source
ON target.order_id = source.order_id
WHEN MATCHED THEN
UPDATE SET status = source.status, updated_at = now()
WHEN NOT MATCHED THEN
INSERT (order_id, status, created_at)
VALUES (source.order_id, source.status, now());The WHEN MATCHED clause handles rows where the target already has a match. WHEN NOT MATCHED handles new rows. You can have multiple WHEN clauses with conditions:
MERGE INTO inventory AS target
USING restocking AS source
ON target.sku = source.sku
WHEN MATCHED AND source.quantity > 0 THEN
UPDATE SET quantity = target.quantity + source.quantity
WHEN MATCHED AND source.quantity = 0 THEN
DELETE
WHEN NOT MATCHED THEN
INSERT (sku, quantity) VALUES (source.sku, source.quantity);This routes each source row to a different action based on both match status and a condition. With INSERT ON CONFLICT you cannot do this in a single statement without multiple CTEs.
PostgreSQL 17: WHEN NOT MATCHED BY SOURCE
PostgreSQL 17, released in September 2024, added a third match condition: WHEN NOT MATCHED BY SOURCE. This fires for target rows that have no matching source row. It is useful for synchronization patterns where you want to delete or deactivate stale records:
MERGE INTO users AS target
USING ldap_export AS source
ON target.email = source.email
WHEN MATCHED THEN
UPDATE SET display_name = source.display_name, active = true
WHEN NOT MATCHED THEN
INSERT (email, display_name, active)
VALUES (source.email, source.display_name, true)
WHEN NOT MATCHED BY SOURCE THEN
UPDATE SET active = false;One statement handles inserts, updates, and soft-deletes based on a full outer join of source and target. Previously this required three separate passes or complex CTEs.
How MERGE Differs from INSERT ON CONFLICT
The most common point of confusion is that MERGE and INSERT ON CONFLICT are not the same thing, even when both are called "upsert".
INSERT ON CONFLICT targets a specific unique constraint or index. The conflict target is precise:
INSERT INTO metrics (host, metric_name, value)
VALUES ('web-01', 'cpu_pct', 82.3)
ON CONFLICT (host, metric_name)
DO UPDATE SET value = EXCLUDED.value, recorded_at = now();MERGE matches on an arbitrary join condition, not a constraint. This is more flexible but also means MERGE does not benefit from the unique constraint to detect conflicts atomically in the same way. For simple single-row upserts against a known unique key, INSERT ON CONFLICT is still simpler and often faster.
MERGE is better when:
- You are processing a batch of rows from another table or CTE
- You need per-row routing (different actions based on match + condition)
- You need the NOT MATCHED BY SOURCE case (PG17+)
- You want self-documenting code that matches the logical intent of "sync these records"
The Partial Unique Index Limitation
INSERT ON CONFLICT works with partial unique indexes:
CREATE UNIQUE INDEX active_users_email ON users(email)
WHERE deleted_at IS NULL;
INSERT INTO users (email, name) VALUES ('[email protected]', 'Alice')
ON CONFLICT (email) WHERE deleted_at IS NULL
DO UPDATE SET name = EXCLUDED.name;MERGE cannot target a partial index as a conflict target. Its matching is purely based on the ON condition, which you write yourself. This is neither better nor worse, just different: with MERGE you write the matching logic explicitly rather than pointing at a constraint.
WHEN MATCHED THEN DO NOTHING
MERGE does not have a direct DO NOTHING equivalent, but you can achieve it by omitting the WHEN MATCHED clause entirely, or by using a condition that never fires:
-- Only insert, never update:
MERGE INTO events AS target
USING new_events AS source
ON target.event_id = source.event_id
WHEN NOT MATCHED THEN
INSERT (event_id, payload) VALUES (source.event_id, source.payload);If the row already exists, no WHEN MATCHED clause fires, so nothing happens. This is the MERGE equivalent of ON CONFLICT DO NOTHING.
What MERGE Does Not Solve
MERGE is not a lock-free operation. Like INSERT ON CONFLICT, it acquires row locks on matched target rows. Under high concurrency with the same target rows, contention is the same as with ON CONFLICT.
MERGE also does not return per-row action information without using triggers or the RETURNING clause (which in PG15 does not distinguish insert from update). If you need to know which rows were inserted versus updated, you need a different approach: use a CTE with INSERT ON CONFLICT and capture the xmax system column, or use triggers.
MERGE is not faster than INSERT ON CONFLICT for single-row upserts. The overhead of the MERGE planner and the join is measurable at small scales. Use MERGE when the expressiveness is worth it, not because you think it is faster.
When to Use MERGE
Use MERGE when you are synchronizing a set of records from a source into a target and need more than one action type. Data pipeline work, ETL loads, incremental sync from external APIs, and permission synchronization are natural fits. The code is clearer because the intent — "merge these records" — is stated directly rather than implied by an INSERT with a fallback.
Use INSERT ON CONFLICT when you have a single-row upsert against a known unique constraint. It is simpler, has been in Postgres since version 9.5, and has better tooling support across ORMs and libraries.
The two are complementary. Postgres now has both, which is the right outcome.
Building something that needs reliable upserts at scale? builds.anethoth.com tracks software projects in progress — real milestones, known limitations, and what the builder needs next.