MERGE SQL Command following SQL:2016

Enterprise / PostgreSQL - Simon Riggs [2ndQuadrant.com] - 2 April 2018 20:04 EDT

MERGE performs actions that modify rows in the target table using a source table or query. MERGE provides a single SQL statement that can conditionally INSERT/UPDATE/DELETE rows a task that would other require multiple PL statements. e.g.

MERGE INTO target AS t USING source AS s ON t.tid = s.sid WHEN MATCHED AND t.balance > s.delta THEN UPDATE SET balance = t.balance - s.delta WHEN MATCHED THEN DELETE WHEN NOT MATCHED AND s.delta > 0 THEN INSERT VALUES (s.sid, s.delta) WHEN NOT MATCHED THEN DO NOTHING;

MERGE works with regular and partitioned tables, including column and row security enforcement, as well as support for row, statement and transition triggers.

MERGE is optimized for OLTP and is parameterizable, though also useful for large scale ETL/ELT. MERGE is not intended to be used in preference to existing single SQL commands for INSERT, UPDATE or DELETE since there is some overhead. MERGE can be used statically from PL/pgSQL.

MERGE does not yet support inheritance, write rules, RETURNING clauses, updatable views or foreign tables. MERGE follows SQL Standard per the most recent SQL:2016.

Includes full tests and documentation, including full isolation tests to demonstrate the concurrent behavior.

This version written from scratch in 2017 by Simon Riggs, using docs and tests originally written in 2009. Later work from Pavan Deolasee has been both complex and deep, leaving the lead author credit now in his hands. Extensive discussion of concurrency from Peter Geoghegan, with thanks for the time and effort contributed.

Various issues reported via sqlsmith by Andreas Seltenreich

Authors: Pavan Deolasee, Simon Riggs Reviewers: Peter Geoghegan, Amit Langote, Tomas Vondra, Simon Riggs

Discussion: https://postgr.es/m/CANP8+jKitBSrB7oTgT9CY2i1ObfOt36z0XMraQc+Xrz8QB0nXA@mail.gmail.com https://postgr.es/m/CAH2-WzkJdBuxj9PO=2QaO9-3h3xGbQPZ34kJH=HukRekwM-GZg@mail.gmail.com

e6597dc353 MERGE SQL Command following SQL:2016
doc/src/sgml/ref/merge.sgml | 603 +++++++
src/backend/executor/nodeMerge.c | 575 +++++++
src/backend/parser/parse_merge.c | 660 +++++++
src/include/executor/nodeMerge.h | 22 +
src/include/parser/parse_merge.h | 19 +
src/test/isolation/expected/merge-delete.out | 97 ++
.../isolation/expected/merge-insert-update.out | 84 +
.../isolation/expected/merge-match-recheck.out | 106 ++
src/test/isolation/expected/merge-update.out | 213 +++
src/test/isolation/specs/merge-delete.spec | 51 +
src/test/isolation/specs/merge-insert-update.spec | 52 +
src/test/isolation/specs/merge-match-recheck.spec | 79 +
src/test/isolation/specs/merge-update.spec | 132 ++
src/test/regress/expected/merge.out | 1811 ++++++++++++++++++++
src/test/regress/sql/merge.sql | 1173 +++++++++++++
15 files changed, 5677 insertions(+)

Upstream: git.postgresql.org


  • Share