Difference between UPSERT and MERGE?

Question

From the PostgreSQL wiki,

MERGE is typically used to merge two tables, and was introduced in the 2003 SQL standard. The REPLACE statement (a MySQL extension) or UPSERT sequence attempts an UPDATE, or on failure, INSERT. This is similar to UPDATE, then for unmatched rows, INSERT. Whether concurrent access allows modifications which could cause row loss is implementation independent.

Further PostgreSQL's INSERT ... ON CONFLICT DO NOTHING/UPDATE is marketed as UPSERT and was added in 9.5

What then is MERGE? And how does it fit into the mix?

Evan Carroll · Accepted Answer · 2019-03-16T22:02:40.870

Generally,

UPSERT is built off of INSERT
MERGE focuses on merging/synchronizing tables and provides
- conditionality (where clauses)
- DELETE support.

UPSERT lacks conditionality, In PostgreSQL, you had some ability to specify conditions, by proxy of the index that was being violated, for instance

ON CONFLICT ON CONSTRAINT countries_pkey DO NOTHING;
ON CONFLICT (country) DO NOTHING;

But it didn't provide the ability to specify multiple conditions nor the ability to DELETE in any condition, both of which permit a more rich set of rules making it possible to "synchronize tables with minimal work" which seems to be the goal of MERGE.

As another matter, from Peter Geoghegan's post highlighting some of the differences "SQL MERGE is quite distinct from UPSERT"

UPSERT is atomic in its insert-or-update
SQL MERGE doesn't technically require a UNIQUE INDEX.

Difference between UPSERT and MERGE?

2 Answers2

See also