From PostgreSQL wiki
Note: MERGE is often used interchangeably with the term UPSERT.
UPSERT functionality will be in the PostgreSQL 9.5 release -- see What's new in PostgreSQL 9.5
MERGE is not in 9.4.5 (the latest PostgreSQL release as of 2015-10-08)
Reference: Merge #1 on most requested features list, with 565 votes as of 5/22/14
Reference: Merge requested feature detail.
Reference: SQL 2011 unsupported features, see feature F312
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.
To implement this cleanly requires that the table have a unique index so duplicate checking can be easily performed. It is possible to do it without a unique index if we require the user to LOCK the table before the MERGE.
Discussion of MERGE progress in PostgreSQL:
- 2005-11-11 - Someone working to add merge?, by Jaime Casanova
- 2005-11-11 - MERGE vs REPLACE, by Peter Eisentraut
- 2008-04-16 - MERGE SQL Statement, by Simon Riggs
- 2008-04-21 - MERGE Specification, by Simon Riggs
- 2008-04-30 - Internal design of MERGE, with Rules, by Simon Riggs
- 2010-05-12 - MERGE Syntax, by Peter Eisentraut
- 2010-05-13 - Add MERGE command GSoC 2010, by Bxzhai
- 2011-01-18 - Discussion of Merge patch challenges, by Greg Smith
- 2012-10-16 - Why is Upsert so complicated?, By depesz Hubert Lubaczewski
- 2013-05-22 - PgCon 2013 - plans for Postgres 9.4, by Dave Page
- 2014-02-15 - PostgreSQL 9.4 - What I Was Hoping For, by Craig Kerstiens
- 2014-02-16 - A case for Upserts, by Armin Ronacher
- 2014-05-22 - PgCon 2014 Talk - Why Upsert is Weird: Track 9.4 Features, by Peter Geoghegan
- 2014-07-20 - SQL MERGE is quite distinct from UPSERT, by Peter Geoghegan
- 2014-10-01 - UPSERT (largely discusses syntax as it relates to recent ON CONFLICT UPDATE effort)
Likely Implementation Order
Note: This section is from 2010 and may not reflect current progress.
Due to the way this feature needs to be implemented internally, the following is the likely sequence in which this feature will be built:
- PostgreSQL doesn't have a good way to lock access to a key value that doesn't exist yet--what other databases call key range locking (SQL Server for example). Improvements to the index implementation are needed to allow this feature.
- Add a subset of MERGE support sufficient to implement REPLACE/UPSERT. The full MERGE feature set is a bit broader than this.
- Document how to use the new MERGE feature in lieu of REPLACE/UPSERT, to make transitions easier for users of other databases. Actually adding other vendor's non-standard syntax to make other apps work transparently is unlikely to be accepted as a patch.
- Complete implementing the entirety of the feature set for SQL MERGE.