Difference between UPSERT and MERGE?

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?

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.

See also

• PostgreSQL patch returned 9/11

• PostgreSQL 11 MERGE syntax (since removed)

StackExchange Database Administrators Q#203292, answer score: 9