commit grep author committer pickaxe re ? search:

MERGE SQL Command following SQL:2016



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

Reviewer: 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



82 files changed: