Add support for MERGE SQL command

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 otherwise
require multiple PL statements.  For example,

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 tables, partitioned tables and inheritance
hierarchies, including column and row security enforcement, as well as
support for row and statement triggers and transition tables therein.

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 from PL/pgSQL.

MERGE does not support targetting updatable views or foreign tables, and
RETURNING clauses are not allowed either.  These limitations are likely
fixable with sufficient effort.  Rewrite rules are also not supported,
but it's not clear that we'd want to support them.

Author: Pavan Deolasee <pavan.deolasee@gmail.com>
Author: Álvaro Herrera <alvherre@alvh.no-ip.org>
Author: Amit Langote <amitlangote09@gmail.com>
Author: Simon Riggs <simon.riggs@enterprisedb.com>
Reviewed-by: Peter Eisentraut <peter.eisentraut@enterprisedb.com>
Reviewed-by: Andres Freund <andres@anarazel.de> (earlier versions)
Reviewed-by: Peter Geoghegan <pg@bowt.ie> (earlier versions)
Reviewed-by: Robert Haas <robertmhaas@gmail.com> (earlier versions)
Reviewed-by: Japin Li <japinli@hotmail.com>
Reviewed-by: Justin Pryzby <pryzby@telsasoft.com>
Reviewed-by: Tomas Vondra <tomas.vondra@enterprisedb.com>
Reviewed-by: Zhihong Yu <zyu@yugabyte.com>
Discussion: https://postgr.es/m/CANP8+jKitBSrB7oTgT9CY2i1ObfOt36z0XMraQc+Xrz8QB0nXA@mail.gmail.com
Discussion: https://postgr.es/m/CAH2-WzkJdBuxj9PO=2QaO9-3h3xGbQPZ34kJH=HukRekwM-GZg@mail.gmail.com
Discussion: https://postgr.es/m/20201231134736.GA25392@alvherre.pgsql

24 files changed, 5180 insertions, 1 deletions

diff --git a/src/test/isolation/expected/merge-delete.out b/src/test/isolation/expected/merge-delete.out
new file mode 100644
index 00000000000..b2befa8e167
--- /dev/null
+++ b/src/test/isolation/expected/merge-delete.out
@@ -0,0 +1,117 @@
+Parsed test spec with 2 sessions
+
+starting permutation: delete c1 select2 c2
+step delete: DELETE FROM target t WHERE t.key = 1;
+step c1: COMMIT;
+step select2: SELECT * FROM target;
+key|val
+---+---
+(0 rows)
+
+step c2: COMMIT;
+
+starting permutation: merge_delete c1 select2 c2
+step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
+step c1: COMMIT;
+step select2: SELECT * FROM target;
+key|val
+---+---
+(0 rows)
+
+step c2: COMMIT;
+
+starting permutation: delete c1 update1 select2 c2
+step delete: DELETE FROM target t WHERE t.key = 1;
+step c1: COMMIT;
+step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1;
+step select2: SELECT * FROM target;
+key|val
+---+---
+(0 rows)
+
+step c2: COMMIT;
+
+starting permutation: merge_delete c1 update1 select2 c2
+step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
+step c1: COMMIT;
+step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1;
+step select2: SELECT * FROM target;
+key|val
+---+---
+(0 rows)
+
+step c2: COMMIT;
+
+starting permutation: delete c1 merge2 select2 c2
+step delete: DELETE FROM target t WHERE t.key = 1;
+step c1: COMMIT;
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+step select2: SELECT * FROM target;
+key|val    
+---+-------
+  1|merge2a
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: merge_delete c1 merge2 select2 c2
+step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
+step c1: COMMIT;
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+step select2: SELECT * FROM target;
+key|val    
+---+-------
+  1|merge2a
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete update1 c1 select2 c2
+step delete: DELETE FROM target t WHERE t.key = 1;
+step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1; <waiting ...>
+step c1: COMMIT;
+step update1: <... completed>
+step select2: SELECT * FROM target;
+key|val
+---+---
+(0 rows)
+
+step c2: COMMIT;
+
+starting permutation: merge_delete update1 c1 select2 c2
+step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
+step update1: UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1; <waiting ...>
+step c1: COMMIT;
+step update1: <... completed>
+step select2: SELECT * FROM target;
+key|val
+---+---
+(0 rows)
+
+step c2: COMMIT;
+
+starting permutation: delete merge2 c1 select2 c2
+step delete: DELETE FROM target t WHERE t.key = 1;
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; <waiting ...>
+step c1: COMMIT;
+step merge2: <... completed>
+step select2: SELECT * FROM target;
+key|val    
+---+-------
+  1|merge2a
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: merge_delete merge2 c1 select2 c2
+step merge_delete: MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE;
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; <waiting ...>
+step c1: COMMIT;
+step merge2: <... completed>
+step select2: SELECT * FROM target;
+key|val    
+---+-------
+  1|merge2a
+(1 row)
+
+step c2: COMMIT;
diff --git a/src/test/isolation/expected/merge-insert-update.out b/src/test/isolation/expected/merge-insert-update.out
new file mode 100644
index 00000000000..ee8b3c4e9fd
--- /dev/null
+++ b/src/test/isolation/expected/merge-insert-update.out
@@ -0,0 +1,94 @@
+Parsed test spec with 2 sessions
+
+starting permutation: merge1 c1 select2 c2
+step merge1: MERGE INTO target t USING (SELECT 1 as key, 'merge1' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge1';
+step c1: COMMIT;
+step select2: SELECT * FROM target;
+key|val   
+---+------
+  1|merge1
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: merge1 c1 merge2 select2 c2
+step merge1: MERGE INTO target t USING (SELECT 1 as key, 'merge1' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge1';
+step c1: COMMIT;
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge2';
+step select2: SELECT * FROM target;
+key|val                     
+---+------------------------
+  1|merge1 updated by merge2
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: insert1 merge2 c1 select2 c2
+step insert1: INSERT INTO target VALUES (1, 'insert1');
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge2'; <waiting ...>
+step c1: COMMIT;
+step merge2: <... completed>
+ERROR:  duplicate key value violates unique constraint "target_pkey"
+step select2: SELECT * FROM target;
+ERROR:  current transaction is aborted, commands ignored until end of transaction block
+step c2: COMMIT;
+
+starting permutation: merge1 merge2 c1 select2 c2
+step merge1: MERGE INTO target t USING (SELECT 1 as key, 'merge1' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge1';
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge2'; <waiting ...>
+step c1: COMMIT;
+step merge2: <... completed>
+ERROR:  duplicate key value violates unique constraint "target_pkey"
+step select2: SELECT * FROM target;
+ERROR:  current transaction is aborted, commands ignored until end of transaction block
+step c2: COMMIT;
+
+starting permutation: merge1 merge2 a1 select2 c2
+step merge1: MERGE INTO target t USING (SELECT 1 as key, 'merge1' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge1';
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge2'; <waiting ...>
+step a1: ABORT;
+step merge2: <... completed>
+step select2: SELECT * FROM target;
+key|val   
+---+------
+  1|merge2
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete1 insert1 c1 merge2 select2 c2
+step delete1: DELETE FROM target WHERE key = 1;
+step insert1: INSERT INTO target VALUES (1, 'insert1');
+step c1: COMMIT;
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge2';
+step select2: SELECT * FROM target;
+key|val                      
+---+-------------------------
+  1|insert1 updated by merge2
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: delete1 insert1 merge2 c1 select2 c2
+step delete1: DELETE FROM target WHERE key = 1;
+step insert1: INSERT INTO target VALUES (1, 'insert1');
+step merge2: MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge2'; <waiting ...>
+step c1: COMMIT;
+step merge2: <... completed>
+ERROR:  duplicate key value violates unique constraint "target_pkey"
+step select2: SELECT * FROM target;
+ERROR:  current transaction is aborted, commands ignored until end of transaction block
+step c2: COMMIT;
+
+starting permutation: delete1 insert1 merge2i c1 select2 c2
+step delete1: DELETE FROM target WHERE key = 1;
+step insert1: INSERT INTO target VALUES (1, 'insert1');
+step merge2i: MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge2';
+step c1: COMMIT;
+step select2: SELECT * FROM target;
+key|val    
+---+-------
+  1|insert1
+(1 row)
+
+step c2: COMMIT;
diff --git a/src/test/isolation/expected/merge-match-recheck.out b/src/test/isolation/expected/merge-match-recheck.out
new file mode 100644
index 00000000000..8183f52ce02
--- /dev/null
+++ b/src/test/isolation/expected/merge-match-recheck.out
@@ -0,0 +1,116 @@
+Parsed test spec with 2 sessions
+
+starting permutation: update1 merge_status c2 select1 c1
+step update1: UPDATE target t SET balance = balance + 10, val = t.val || ' updated by update1' WHERE t.key = 1;
+step merge_status: 
+  MERGE INTO target t
+  USING (SELECT 1 as key) s
+  ON s.key = t.key
+  WHEN MATCHED AND status = 's1' THEN
+	UPDATE SET status = 's2', val = t.val || ' when1'
+  WHEN MATCHED AND status = 's2' THEN
+	UPDATE SET status = 's3', val = t.val || ' when2'
+  WHEN MATCHED AND status = 's3' THEN
+	UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+step merge_status: <... completed>
+step select1: SELECT * FROM target;
+key|balance|status|val                           
+---+-------+------+------------------------------
+  1|    170|s2    |setup updated by update1 when1
+(1 row)
+
+step c1: COMMIT;
+
+starting permutation: update2 merge_status c2 select1 c1
+step update2: UPDATE target t SET status = 's2', val = t.val || ' updated by update2' WHERE t.key = 1;
+step merge_status: 
+  MERGE INTO target t
+  USING (SELECT 1 as key) s
+  ON s.key = t.key
+  WHEN MATCHED AND status = 's1' THEN
+	UPDATE SET status = 's2', val = t.val || ' when1'
+  WHEN MATCHED AND status = 's2' THEN
+	UPDATE SET status = 's3', val = t.val || ' when2'
+  WHEN MATCHED AND status = 's3' THEN
+	UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+step merge_status: <... completed>
+step select1: SELECT * FROM target;
+key|balance|status|val                           
+---+-------+------+------------------------------
+  1|    160|s3    |setup updated by update2 when2
+(1 row)
+
+step c1: COMMIT;
+
+starting permutation: update3 merge_status c2 select1 c1
+step update3: UPDATE target t SET status = 's3', val = t.val || ' updated by update3' WHERE t.key = 1;
+step merge_status: 
+  MERGE INTO target t
+  USING (SELECT 1 as key) s
+  ON s.key = t.key
+  WHEN MATCHED AND status = 's1' THEN
+	UPDATE SET status = 's2', val = t.val || ' when1'
+  WHEN MATCHED AND status = 's2' THEN
+	UPDATE SET status = 's3', val = t.val || ' when2'
+  WHEN MATCHED AND status = 's3' THEN
+	UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+step merge_status: <... completed>
+step select1: SELECT * FROM target;
+key|balance|status|val                           
+---+-------+------+------------------------------
+  1|    160|s4    |setup updated by update3 when3
+(1 row)
+
+step c1: COMMIT;
+
+starting permutation: update5 merge_status c2 select1 c1
+step update5: UPDATE target t SET status = 's5', val = t.val || ' updated by update5' WHERE t.key = 1;
+step merge_status: 
+  MERGE INTO target t
+  USING (SELECT 1 as key) s
+  ON s.key = t.key
+  WHEN MATCHED AND status = 's1' THEN
+	UPDATE SET status = 's2', val = t.val || ' when1'
+  WHEN MATCHED AND status = 's2' THEN
+	UPDATE SET status = 's3', val = t.val || ' when2'
+  WHEN MATCHED AND status = 's3' THEN
+	UPDATE SET status = 's4', val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+step merge_status: <... completed>
+step select1: SELECT * FROM target;
+key|balance|status|val                     
+---+-------+------+------------------------
+  1|    160|s5    |setup updated by update5
+(1 row)
+
+step c1: COMMIT;
+
+starting permutation: update_bal1 merge_bal c2 select1 c1
+step update_bal1: UPDATE target t SET balance = 50, val = t.val || ' updated by update_bal1' WHERE t.key = 1;
+step merge_bal: 
+  MERGE INTO target t
+  USING (SELECT 1 as key) s
+  ON s.key = t.key
+  WHEN MATCHED AND balance < 100 THEN
+	UPDATE SET balance = balance * 2, val = t.val || ' when1'
+  WHEN MATCHED AND balance < 200 THEN
+	UPDATE SET balance = balance * 4, val = t.val || ' when2'
+  WHEN MATCHED AND balance < 300 THEN
+	UPDATE SET balance = balance * 8, val = t.val || ' when3';
+ <waiting ...>
+step c2: COMMIT;
+step merge_bal: <... completed>
+step select1: SELECT * FROM target;
+key|balance|status|val                               
+---+-------+------+----------------------------------
+  1|    100|s1    |setup updated by update_bal1 when1
+(1 row)
+
+step c1: COMMIT;
diff --git a/src/test/isolation/expected/merge-update.out b/src/test/isolation/expected/merge-update.out
new file mode 100644
index 00000000000..55b1f908fdd
--- /dev/null
+++ b/src/test/isolation/expected/merge-update.out
@@ -0,0 +1,314 @@
+Parsed test spec with 2 sessions
+
+starting permutation: merge1 c1 select2 c2
+step merge1: 
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge1' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+    UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+
+step c1: COMMIT;
+step select2: SELECT * FROM target;
+key|val                     
+---+------------------------
+  2|setup1 updated by merge1
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: merge1 c1 merge2a select2 c2
+step merge1: 
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge1' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+    UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+
+step c1: COMMIT;
+step merge2a: 
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge2a' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+
+step select2: SELECT * FROM target;
+key|val                     
+---+------------------------
+  2|setup1 updated by merge1
+  1|merge2a                 
+(2 rows)
+
+step c2: COMMIT;
+
+starting permutation: merge1 merge2a c1 select2 c2
+step merge1: 
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge1' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+    UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+
+step merge2a: 
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge2a' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+ <waiting ...>
+step c1: COMMIT;
+step merge2a: <... completed>
+step select2: SELECT * FROM target;
+key|val                     
+---+------------------------
+  2|setup1 updated by merge1
+  1|merge2a                 
+(2 rows)
+
+step c2: COMMIT;
+
+starting permutation: merge1 merge2a a1 select2 c2
+step merge1: 
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge1' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+    UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+
+step merge2a: 
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge2a' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+ <waiting ...>
+step a1: ABORT;
+step merge2a: <... completed>
+step select2: SELECT * FROM target;
+key|val                      
+---+-------------------------
+  2|setup1 updated by merge2a
+(1 row)
+
+step c2: COMMIT;
+
+starting permutation: merge1 merge2b c1 select2 c2
+step merge1: 
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge1' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+    UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+
+step merge2b: 
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge2b' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED AND t.key < 2 THEN
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+ <waiting ...>
+step c1: COMMIT;
+step merge2b: <... completed>
+step select2: SELECT * FROM target;
+key|val                     
+---+------------------------
+  2|setup1 updated by merge1
+  1|merge2b                 
+(2 rows)
+
+step c2: COMMIT;
+
+starting permutation: merge1 merge2c c1 select2 c2
+step merge1: 
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge1' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+    UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+
+step merge2c: 
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge2c' as val) s
+  ON s.key = t.key AND t.key < 2
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+ <waiting ...>
+step c1: COMMIT;
+step merge2c: <... completed>
+step select2: SELECT * FROM target;
+key|val                     
+---+------------------------
+  2|setup1 updated by merge1
+  1|merge2c                 
+(2 rows)
+
+step c2: COMMIT;
+
+starting permutation: pa_merge1 pa_merge2a c1 pa_select2 c2
+step pa_merge1: 
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge1' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+    UPDATE set val = t.val || ' updated by ' || s.val;
+
+step pa_merge2a: 
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge2a' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+ <waiting ...>
+step c1: COMMIT;
+step pa_merge2a: <... completed>
+step pa_select2: SELECT * FROM pa_target;
+key|val                                               
+---+--------------------------------------------------
+  2|initial                                           
+  2|initial updated by pa_merge1 updated by pa_merge2a
+(2 rows)
+
+step c2: COMMIT;
+
+starting permutation: pa_merge2 pa_merge2a c1 pa_select2 c2
+step pa_merge2: 
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge2' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+    UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+
+step pa_merge2a: 
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge2a' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+ <waiting ...>
+step c1: COMMIT;
+step pa_merge2a: <... completed>
+ERROR:  tuple to be locked was already moved to another partition due to concurrent update
+step pa_select2: SELECT * FROM pa_target;
+ERROR:  current transaction is aborted, commands ignored until end of transaction block
+step c2: COMMIT;
+
+starting permutation: pa_merge2 c1 pa_merge2a pa_select2 c2
+step pa_merge2: 
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge2' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+    UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+
+step c1: COMMIT;
+step pa_merge2a: 
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge2a' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+
+step pa_select2: SELECT * FROM pa_target;
+key|val                         
+---+----------------------------
+  1|pa_merge2a                  
+  2|initial                     
+  2|initial updated by pa_merge2
+(3 rows)
+
+step c2: COMMIT;
+
+starting permutation: pa_merge3 pa_merge2b_when c1 pa_select2 c2
+step pa_merge3: 
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge2' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+    UPDATE set val = 'prefix ' || t.val;
+
+step pa_merge2b_when: 
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge2b_when' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED AND t.val like 'initial%' THEN
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+ <waiting ...>
+step c1: COMMIT;
+step pa_merge2b_when: <... completed>
+step pa_select2: SELECT * FROM pa_target;
+key|val           
+---+--------------
+  1|prefix initial
+  2|initial       
+(2 rows)
+
+step c2: COMMIT;
+
+starting permutation: pa_merge1 pa_merge2b_when c1 pa_select2 c2
+step pa_merge1: 
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge1' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+    UPDATE set val = t.val || ' updated by ' || s.val;
+
+step pa_merge2b_when: 
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge2b_when' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED AND t.val like 'initial%' THEN
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+ <waiting ...>
+step c1: COMMIT;
+step pa_merge2b_when: <... completed>
+step pa_select2: SELECT * FROM pa_target;
+key|val                                                    
+---+-------------------------------------------------------
+  2|initial                                                
+  2|initial updated by pa_merge1 updated by pa_merge2b_when
+(2 rows)
+
+step c2: COMMIT;
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index 8e870981501..00749a40bdd 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -45,6 +45,10 @@ test: insert-conflict-do-update
 test: insert-conflict-do-update-2
 test: insert-conflict-do-update-3
 test: insert-conflict-specconflict
+test: merge-insert-update
+test: merge-delete
+test: merge-update
+test: merge-match-recheck
 test: delete-abort-savept
 test: delete-abort-savept-2
 test: aborted-keyrevoke
diff --git a/src/test/isolation/specs/merge-delete.spec b/src/test/isolation/specs/merge-delete.spec
new file mode 100644
index 00000000000..0e7053270ec
--- /dev/null
+++ b/src/test/isolation/specs/merge-delete.spec
@@ -0,0 +1,50 @@
+# MERGE DELETE
+#
+# This test looks at the interactions involving concurrent deletes
+# comparing the behavior of MERGE, DELETE and UPDATE
+
+setup
+{
+  CREATE TABLE target (key int primary key, val text);
+  INSERT INTO target VALUES (1, 'setup1');
+}
+
+teardown
+{
+  DROP TABLE target;
+}
+
+session "s1"
+setup
+{
+  BEGIN ISOLATION LEVEL READ COMMITTED;
+}
+step "delete" { DELETE FROM target t WHERE t.key = 1; }
+step "merge_delete" { MERGE INTO target t USING (SELECT 1 as key) s ON s.key = t.key WHEN MATCHED THEN DELETE; }
+step "c1" { COMMIT; }
+
+session "s2"
+setup
+{
+  BEGIN ISOLATION LEVEL READ COMMITTED;
+}
+step "update1" { UPDATE target t SET val = t.val || ' updated by update1' WHERE t.key = 1; }
+step "merge2" { MERGE INTO target t USING (SELECT 1 as key, 'merge2a' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val; }
+step "select2" { SELECT * FROM target; }
+step "c2" { COMMIT; }
+
+# Basic effects
+permutation "delete" "c1" "select2" "c2"
+permutation "merge_delete" "c1" "select2" "c2"
+
+# One after the other, no concurrency
+permutation "delete" "c1" "update1" "select2" "c2"
+permutation "merge_delete" "c1" "update1" "select2" "c2"
+permutation "delete" "c1" "merge2" "select2" "c2"
+permutation "merge_delete" "c1" "merge2" "select2" "c2"
+
+# Now with concurrency
+permutation "delete" "update1" "c1" "select2" "c2"
+permutation "merge_delete" "update1" "c1" "select2" "c2"
+permutation "delete" "merge2" "c1" "select2" "c2"
+permutation "merge_delete" "merge2" "c1" "select2" "c2"
diff --git a/src/test/isolation/specs/merge-insert-update.spec b/src/test/isolation/specs/merge-insert-update.spec
new file mode 100644
index 00000000000..1bf1ed461db
--- /dev/null
+++ b/src/test/isolation/specs/merge-insert-update.spec
@@ -0,0 +1,51 @@
+# MERGE INSERT UPDATE
+#
+# This looks at how we handle concurrent INSERTs, illustrating how the
+# behavior differs from INSERT ... ON CONFLICT
+
+setup
+{
+  CREATE TABLE target (key int primary key, val text);
+}
+
+teardown
+{
+  DROP TABLE target;
+}
+
+session "s1"
+setup
+{
+  BEGIN ISOLATION LEVEL READ COMMITTED;
+}
+step "merge1" { MERGE INTO target t USING (SELECT 1 as key, 'merge1' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge1'; }
+step "delete1" { DELETE FROM target WHERE key = 1; }
+step "insert1" { INSERT INTO target VALUES (1, 'insert1'); }
+step "c1" { COMMIT; }
+step "a1" { ABORT; }
+
+session "s2"
+setup
+{
+  BEGIN ISOLATION LEVEL READ COMMITTED;
+}
+step "merge2" { MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN NOT MATCHED THEN INSERT VALUES (s.key, s.val) WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge2'; }
+
+step "merge2i" { MERGE INTO target t USING (SELECT 1 as key, 'merge2' as val) s ON s.key = t.key WHEN MATCHED THEN UPDATE set val = t.val || ' updated by merge2'; }
+
+step "select2" { SELECT * FROM target; }
+step "c2" { COMMIT; }
+
+# Basic effects
+permutation "merge1" "c1" "select2" "c2"
+permutation "merge1" "c1" "merge2" "select2" "c2"
+
+# check concurrent inserts
+permutation "insert1" "merge2" "c1" "select2" "c2"
+permutation "merge1" "merge2" "c1" "select2" "c2"
+permutation "merge1" "merge2" "a1" "select2" "c2"
+
+# check how we handle when visible row has been concurrently deleted, then same key re-inserted
+permutation "delete1" "insert1" "c1" "merge2" "select2" "c2"
+permutation "delete1" "insert1" "merge2" "c1" "select2" "c2"
+permutation "delete1" "insert1" "merge2i" "c1" "select2" "c2"
diff --git a/src/test/isolation/specs/merge-match-recheck.spec b/src/test/isolation/specs/merge-match-recheck.spec
new file mode 100644
index 00000000000..d56400a6a22
--- /dev/null
+++ b/src/test/isolation/specs/merge-match-recheck.spec
@@ -0,0 +1,77 @@
+# MERGE MATCHED RECHECK
+#
+# This test looks at what happens when we have complex
+# WHEN MATCHED AND conditions and a concurrent UPDATE causes a
+# recheck of the AND condition on the new row
+
+setup
+{
+  CREATE TABLE target (key int primary key, balance integer, status text, val text);
+  INSERT INTO target VALUES (1, 160, 's1', 'setup');
+}
+
+teardown
+{
+  DROP TABLE target;
+}
+
+session "s1"
+setup
+{
+  BEGIN ISOLATION LEVEL READ COMMITTED;
+}
+step "merge_status"
+{
+  MERGE INTO target t
+  USING (SELECT 1 as key) s
+  ON s.key = t.key
+  WHEN MATCHED AND status = 's1' THEN
+	UPDATE SET status = 's2', val = t.val || ' when1'
+  WHEN MATCHED AND status = 's2' THEN
+	UPDATE SET status = 's3', val = t.val || ' when2'
+  WHEN MATCHED AND status = 's3' THEN
+	UPDATE SET status = 's4', val = t.val || ' when3';
+}
+
+step "merge_bal"
+{
+  MERGE INTO target t
+  USING (SELECT 1 as key) s
+  ON s.key = t.key
+  WHEN MATCHED AND balance < 100 THEN
+	UPDATE SET balance = balance * 2, val = t.val || ' when1'
+  WHEN MATCHED AND balance < 200 THEN
+	UPDATE SET balance = balance * 4, val = t.val || ' when2'
+  WHEN MATCHED AND balance < 300 THEN
+	UPDATE SET balance = balance * 8, val = t.val || ' when3';
+}
+
+step "select1" { SELECT * FROM target; }
+step "c1" { COMMIT; }
+
+session "s2"
+setup
+{
+  BEGIN ISOLATION LEVEL READ COMMITTED;
+}
+step "update1" { UPDATE target t SET balance = balance + 10, val = t.val || ' updated by update1' WHERE t.key = 1; }
+step "update2" { UPDATE target t SET status = 's2', val = t.val || ' updated by update2' WHERE t.key = 1; }
+step "update3" { UPDATE target t SET status = 's3', val = t.val || ' updated by update3' WHERE t.key = 1; }
+step "update5" { UPDATE target t SET status = 's5', val = t.val || ' updated by update5' WHERE t.key = 1; }
+step "update_bal1" { UPDATE target t SET balance = 50, val = t.val || ' updated by update_bal1' WHERE t.key = 1; }
+step "c2" { COMMIT; }
+
+# merge_status sees concurrently updated row and rechecks WHEN conditions, but recheck passes and final status = 's2'
+permutation "update1" "merge_status" "c2" "select1" "c1"
+
+# merge_status sees concurrently updated row and rechecks WHEN conditions, recheck fails, so final status = 's3' not 's2'
+permutation "update2" "merge_status" "c2" "select1" "c1"
+
+# merge_status sees concurrently updated row and rechecks WHEN conditions, recheck fails, so final status = 's4' not 's2'
+permutation "update3" "merge_status" "c2" "select1" "c1"
+
+# merge_status sees concurrently updated row and rechecks WHEN conditions, recheck fails, but we skip update and MERGE does nothing
+permutation "update5" "merge_status" "c2" "select1" "c1"
+
+# merge_bal sees concurrently updated row and rechecks WHEN conditions, recheck fails, so final balance = 100 not 640
+permutation "update_bal1" "merge_bal" "c2" "select1" "c1"
diff --git a/src/test/isolation/specs/merge-update.spec b/src/test/isolation/specs/merge-update.spec
new file mode 100644
index 00000000000..e8d01666fe2
--- /dev/null
+++ b/src/test/isolation/specs/merge-update.spec
@@ -0,0 +1,156 @@
+# MERGE UPDATE
+#
+# This test exercises atypical cases
+# 1. UPDATEs of PKs that change the join in the ON clause
+# 2. UPDATEs with WHEN conditions that would fail after concurrent update
+# 3. UPDATEs with extra ON conditions that would fail after concurrent update
+
+setup
+{
+  CREATE TABLE target (key int primary key, val text);
+  INSERT INTO target VALUES (1, 'setup1');
+
+  CREATE TABLE pa_target (key integer, val text)
+	  PARTITION BY LIST (key);
+  CREATE TABLE part1 (key integer, val text);
+  CREATE TABLE part2 (val text, key integer);
+  CREATE TABLE part3 (key integer, val text);
+
+  ALTER TABLE pa_target ATTACH PARTITION part1 FOR VALUES IN (1,4);
+  ALTER TABLE pa_target ATTACH PARTITION part2 FOR VALUES IN (2,5,6);
+  ALTER TABLE pa_target ATTACH PARTITION part3 DEFAULT;
+
+  INSERT INTO pa_target VALUES (1, 'initial');
+  INSERT INTO pa_target VALUES (2, 'initial');
+}
+
+teardown
+{
+  DROP TABLE target;
+  DROP TABLE pa_target CASCADE;
+}
+
+session "s1"
+setup
+{
+  BEGIN ISOLATION LEVEL READ COMMITTED;
+}
+step "merge1"
+{
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge1' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+    UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+}
+step "pa_merge1"
+{
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge1' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+    UPDATE set val = t.val || ' updated by ' || s.val;
+}
+step "pa_merge2"
+{
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge2' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+    UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+}
+step "pa_merge3"
+{
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge2' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+    UPDATE set val = 'prefix ' || t.val;
+}
+step "c1" { COMMIT; }
+step "a1" { ABORT; }
+
+session "s2"
+setup
+{
+  BEGIN ISOLATION LEVEL READ COMMITTED;
+}
+step "merge2a"
+{
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge2a' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+}
+step "merge2b"
+{
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge2b' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED AND t.key < 2 THEN
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+}
+step "merge2c"
+{
+  MERGE INTO target t
+  USING (SELECT 1 as key, 'merge2c' as val) s
+  ON s.key = t.key AND t.key < 2
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+}
+step "pa_merge2a"
+{
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge2a' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED THEN
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+}
+# MERGE proceeds only if 'val' unchanged
+step "pa_merge2b_when"
+{
+  MERGE INTO pa_target t
+  USING (SELECT 1 as key, 'pa_merge2b_when' as val) s
+  ON s.key = t.key
+  WHEN NOT MATCHED THEN
+	INSERT VALUES (s.key, s.val)
+  WHEN MATCHED AND t.val like 'initial%' THEN
+	UPDATE set key = t.key + 1, val = t.val || ' updated by ' || s.val;
+}
+step "select2" { SELECT * FROM target; }
+step "pa_select2" { SELECT * FROM pa_target; }
+step "c2" { COMMIT; }
+
+# Basic effects
+permutation "merge1" "c1" "select2" "c2"
+
+# One after the other, no concurrency
+permutation "merge1" "c1" "merge2a" "select2" "c2"
+
+# Now with concurrency
+permutation "merge1" "merge2a" "c1" "select2" "c2"
+permutation "merge1" "merge2a" "a1" "select2" "c2"
+permutation "merge1" "merge2b" "c1" "select2" "c2"
+permutation "merge1" "merge2c" "c1" "select2" "c2"
+permutation "pa_merge1" "pa_merge2a" "c1" "pa_select2" "c2"
+permutation "pa_merge2" "pa_merge2a" "c1" "pa_select2" "c2" # fails
+permutation "pa_merge2" "c1" "pa_merge2a" "pa_select2" "c2" # succeeds
+permutation "pa_merge3" "pa_merge2b_when" "c1" "pa_select2" "c2" # WHEN not satisfied by updated tuple
+permutation "pa_merge1" "pa_merge2b_when" "c1" "pa_select2" "c2" # WHEN satisfied by updated tuple
diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out
index 99811570b7b..5f03d8e14fb 100644
--- a/src/test/regress/expected/identity.out
+++ b/src/test/regress/expected/identity.out
@@ -560,3 +560,57 @@ CREATE TABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NOT NULL);
 DROP TABLE itest15;
 CREATE TABLE itest15 (id integer NOT NULL GENERATED ALWAYS AS IDENTITY);
 DROP TABLE itest15;
+-- MERGE tests
+CREATE TABLE itest15 (a int GENERATED ALWAYS AS IDENTITY, b text);
+CREATE TABLE itest16 (a int GENERATED BY DEFAULT AS IDENTITY, b text);
+MERGE INTO itest15 t
+USING (SELECT 10 AS s_a, 'inserted by merge' AS s_b) s
+ON t.a = s.s_a
+WHEN NOT MATCHED THEN
+	INSERT (a, b) VALUES (s.s_a, s.s_b);
+ERROR:  cannot insert a non-DEFAULT value into column "a"
+DETAIL:  Column "a" is an identity column defined as GENERATED ALWAYS.
+HINT:  Use OVERRIDING SYSTEM VALUE to override.
+-- Used to fail, but now it works and ignores the user supplied value
+MERGE INTO itest15 t
+USING (SELECT 20 AS s_a, 'inserted by merge' AS s_b) s
+ON t.a = s.s_a
+WHEN NOT MATCHED THEN
+	INSERT (a, b) OVERRIDING USER VALUE VALUES (s.s_a, s.s_b);
+MERGE INTO itest15 t
+USING (SELECT 30 AS s_a, 'inserted by merge' AS s_b) s
+ON t.a = s.s_a
+WHEN NOT MATCHED THEN
+	INSERT (a, b) OVERRIDING SYSTEM VALUE VALUES (s.s_a, s.s_b);
+MERGE INTO itest16 t
+USING (SELECT 10 AS s_a, 'inserted by merge' AS s_b) s
+ON t.a = s.s_a
+WHEN NOT MATCHED THEN
+	INSERT (a, b) VALUES (s.s_a, s.s_b);
+MERGE INTO itest16 t
+USING (SELECT 20 AS s_a, 'inserted by merge' AS s_b) s
+ON t.a = s.s_a
+WHEN NOT MATCHED THEN
+	INSERT (a, b) OVERRIDING USER VALUE VALUES (s.s_a, s.s_b);
+MERGE INTO itest16 t
+USING (SELECT 30 AS s_a, 'inserted by merge' AS s_b) s
+ON t.a = s.s_a
+WHEN NOT MATCHED THEN
+	INSERT (a, b) OVERRIDING SYSTEM VALUE VALUES (s.s_a, s.s_b);
+SELECT * FROM itest15;
+ a  |         b         
+----+-------------------
+  1 | inserted by merge
+ 30 | inserted by merge
+(2 rows)
+
+SELECT * FROM itest16;
+ a  |         b         
+----+-------------------
+ 10 | inserted by merge
+  1 | inserted by merge
+ 30 | inserted by merge
+(3 rows)
+
+DROP TABLE itest15;
+DROP TABLE itest16;
diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out
new file mode 100644
index 00000000000..da8796986ff
--- /dev/null
+++ b/src/test/regress/expected/merge.out
@@ -0,0 +1,1934 @@
+--
+-- MERGE
+--
+--\set VERBOSITY verbose
+--set debug_print_rewritten = true;
+--set debug_print_parse = true;
+--set debug_print_pretty = true;
+CREATE USER merge_privs;
+CREATE USER merge_no_privs;
+DROP TABLE IF EXISTS target;
+NOTICE:  table "target" does not exist, skipping
+DROP TABLE IF EXISTS source;
+NOTICE:  table "source" does not exist, skipping
+CREATE TABLE target (tid integer, balance integer);
+CREATE TABLE source (sid integer, delta integer); --no index
+INSERT INTO target VALUES (1, 10);
+INSERT INTO target VALUES (2, 20);
+INSERT INTO target VALUES (3, 30);
+SELECT t.ctid is not null as matched, t.*, s.* FROM source s FULL OUTER JOIN target t ON s.sid = t.tid ORDER BY t.tid, s.sid;
+ matched | tid | balance | sid | delta 
+---------+-----+---------+-----+-------
+ t       |   1 |      10 |     |      
+ t       |   2 |      20 |     |      
+ t       |   3 |      30 |     |      
+(3 rows)
+
+ALTER TABLE target OWNER TO merge_privs;
+ALTER TABLE source OWNER TO merge_privs;
+CREATE TABLE target2 (tid integer, balance integer);
+CREATE TABLE source2 (sid integer, delta integer);
+ALTER TABLE target2 OWNER TO merge_no_privs;
+ALTER TABLE source2 OWNER TO merge_no_privs;
+GRANT INSERT ON target TO merge_no_privs;
+SET SESSION AUTHORIZATION merge_privs;
+EXPLAIN (COSTS OFF)
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	DELETE;
+               QUERY PLAN               
+----------------------------------------
+ Merge on target t
+   ->  Merge Join
+         Merge Cond: (t.tid = s.sid)
+         ->  Sort
+               Sort Key: t.tid
+               ->  Seq Scan on target t
+         ->  Sort
+               Sort Key: s.sid
+               ->  Seq Scan on source s
+(9 rows)
+
+--
+-- Errors
+--
+MERGE INTO target t RANDOMWORD
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = 0;
+ERROR:  syntax error at or near "RANDOMWORD"
+LINE 1: MERGE INTO target t RANDOMWORD
+                            ^
+-- MATCHED/INSERT error
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	INSERT DEFAULT VALUES;
+ERROR:  syntax error at or near "INSERT"
+LINE 5:  INSERT DEFAULT VALUES;
+         ^
+-- incorrectly specifying INTO target
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT INTO target DEFAULT VALUES;
+ERROR:  syntax error at or near "INTO"
+LINE 5:  INSERT INTO target DEFAULT VALUES;
+                ^
+-- Multiple VALUES clause
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT VALUES (1,1), (2,2);
+ERROR:  syntax error at or near ","
+LINE 5:  INSERT VALUES (1,1), (2,2);
+                            ^
+-- SELECT query for INSERT
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT SELECT (1, 1);
+ERROR:  syntax error at or near "SELECT"
+LINE 5:  INSERT SELECT (1, 1);
+                ^
+-- NOT MATCHED/UPDATE
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	UPDATE SET balance = 0;
+ERROR:  syntax error at or near "UPDATE"
+LINE 5:  UPDATE SET balance = 0;
+         ^
+-- UPDATE tablename
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	UPDATE target SET balance = 0;
+ERROR:  syntax error at or near "target"
+LINE 5:  UPDATE target SET balance = 0;
+                ^
+-- source and target names the same
+MERGE INTO target
+USING target
+ON tid = tid
+WHEN MATCHED THEN DO NOTHING;
+ERROR:  name "target" specified more than once
+DETAIL:  The name is used both as MERGE target table and data source.
+-- unsupported relation types
+-- view
+CREATE VIEW tv AS SELECT * FROM target;
+MERGE INTO tv t
+USING source s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT DEFAULT VALUES;
+ERROR:  cannot execute MERGE on relation "tv"
+DETAIL:  This operation is not supported for views.
+DROP VIEW tv;
+-- materialized view
+CREATE MATERIALIZED VIEW mv AS SELECT * FROM target;
+MERGE INTO mv t
+USING source s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT DEFAULT VALUES;
+ERROR:  cannot execute MERGE on relation "mv"
+DETAIL:  This operation is not supported for materialized views.
+DROP MATERIALIZED VIEW mv;
+-- permissions
+MERGE INTO target
+USING source2
+ON target.tid = source2.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = 0;
+ERROR:  permission denied for table source2
+GRANT INSERT ON target TO merge_no_privs;
+SET SESSION AUTHORIZATION merge_no_privs;
+MERGE INTO target
+USING source2
+ON target.tid = source2.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = 0;
+ERROR:  permission denied for table target
+GRANT UPDATE ON target2 TO merge_privs;
+SET SESSION AUTHORIZATION merge_privs;
+MERGE INTO target2
+USING source
+ON target2.tid = source.sid
+WHEN MATCHED THEN
+	DELETE;
+ERROR:  permission denied for table target2
+MERGE INTO target2
+USING source
+ON target2.tid = source.sid
+WHEN NOT MATCHED THEN
+	INSERT DEFAULT VALUES;
+ERROR:  permission denied for table target2
+-- check if the target can be accessed from source relation subquery; we should
+-- not be able to do so
+MERGE INTO target t
+USING (SELECT * FROM source WHERE t.tid > sid) s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT DEFAULT VALUES;
+ERROR:  invalid reference to FROM-clause entry for table "t"
+LINE 2: USING (SELECT * FROM source WHERE t.tid > sid) s
+                                          ^
+HINT:  There is an entry for table "t", but it cannot be referenced from this part of the query.
+--
+-- initial tests
+--
+-- zero rows in source has no effect
+MERGE INTO target
+USING source
+ON target.tid = source.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = 0;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = 0;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	DELETE;
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT DEFAULT VALUES;
+ROLLBACK;
+-- insert some non-matching source rows to work from
+INSERT INTO source VALUES (4, 40);
+SELECT * FROM source ORDER BY sid;
+ sid | delta 
+-----+-------
+   4 |    40
+(1 row)
+
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |      10
+   2 |      20
+   3 |      30
+(3 rows)
+
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	DO NOTHING;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = 0;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	DELETE;
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT DEFAULT VALUES;
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |      10
+   2 |      20
+   3 |      30
+     |        
+(4 rows)
+
+ROLLBACK;
+-- index plans
+INSERT INTO target SELECT generate_series(1000,2500), 0;
+ALTER TABLE target ADD PRIMARY KEY (tid);
+ANALYZE target;
+EXPLAIN (COSTS OFF)
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = 0;
+               QUERY PLAN               
+----------------------------------------
+ Merge on target t
+   ->  Hash Join
+         Hash Cond: (s.sid = t.tid)
+         ->  Seq Scan on source s
+         ->  Hash
+               ->  Seq Scan on target t
+(6 rows)
+
+EXPLAIN (COSTS OFF)
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	DELETE;
+               QUERY PLAN               
+----------------------------------------
+ Merge on target t
+   ->  Hash Join
+         Hash Cond: (s.sid = t.tid)
+         ->  Seq Scan on source s
+         ->  Hash
+               ->  Seq Scan on target t
+(6 rows)
+
+EXPLAIN (COSTS OFF)
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT VALUES (4, NULL);
+               QUERY PLAN               
+----------------------------------------
+ Merge on target t
+   ->  Hash Left Join
+         Hash Cond: (s.sid = t.tid)
+         ->  Seq Scan on source s
+         ->  Hash
+               ->  Seq Scan on target t
+(6 rows)
+
+DELETE FROM target WHERE tid > 100;
+ANALYZE target;
+-- insert some matching source rows to work from
+INSERT INTO source VALUES (2, 5);
+INSERT INTO source VALUES (3, 20);
+SELECT * FROM source ORDER BY sid;
+ sid | delta 
+-----+-------
+   2 |     5
+   3 |    20
+   4 |    40
+(3 rows)
+
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |      10
+   2 |      20
+   3 |      30
+(3 rows)
+
+-- equivalent of an UPDATE join
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = 0;
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |      10
+   2 |       0
+   3 |       0
+(3 rows)
+
+ROLLBACK;
+-- equivalent of a DELETE join
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	DELETE;
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |      10
+(1 row)
+
+ROLLBACK;
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	DO NOTHING;
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |      10
+   2 |      20
+   3 |      30
+(3 rows)
+
+ROLLBACK;
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT VALUES (4, NULL);
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |      10
+   2 |      20
+   3 |      30
+   4 |        
+(4 rows)
+
+ROLLBACK;
+-- duplicate source row causes multiple target row update ERROR
+INSERT INTO source VALUES (2, 5);
+SELECT * FROM source ORDER BY sid;
+ sid | delta 
+-----+-------
+   2 |     5
+   2 |     5
+   3 |    20
+   4 |    40
+(4 rows)
+
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |      10
+   2 |      20
+   3 |      30
+(3 rows)
+
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = 0;
+ERROR:  MERGE command cannot affect row a second time
+HINT:  Ensure that not more than one source row matches any one target row.
+ROLLBACK;
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	DELETE;
+ERROR:  MERGE command cannot affect row a second time
+HINT:  Ensure that not more than one source row matches any one target row.
+ROLLBACK;
+-- remove duplicate MATCHED data from source data
+DELETE FROM source WHERE sid = 2;
+INSERT INTO source VALUES (2, 5);
+SELECT * FROM source ORDER BY sid;
+ sid | delta 
+-----+-------
+   2 |     5
+   3 |    20
+   4 |    40
+(3 rows)
+
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |      10
+   2 |      20
+   3 |      30
+(3 rows)
+
+-- duplicate source row on INSERT should fail because of target_pkey
+INSERT INTO source VALUES (4, 40);
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+  INSERT VALUES (4, NULL);
+ERROR:  duplicate key value violates unique constraint "target_pkey"
+DETAIL:  Key (tid)=(4) already exists.
+SELECT * FROM target ORDER BY tid;
+ERROR:  current transaction is aborted, commands ignored until end of transaction block
+ROLLBACK;
+-- remove duplicate NOT MATCHED data from source data
+DELETE FROM source WHERE sid = 4;
+INSERT INTO source VALUES (4, 40);
+SELECT * FROM source ORDER BY sid;
+ sid | delta 
+-----+-------
+   2 |     5
+   3 |    20
+   4 |    40
+(3 rows)
+
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |      10
+   2 |      20
+   3 |      30
+(3 rows)
+
+-- remove constraints
+alter table target drop CONSTRAINT target_pkey;
+alter table target alter column tid drop not null;
+-- multiple actions
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT VALUES (4, 4)
+WHEN MATCHED THEN
+	UPDATE SET balance = 0;
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |      10
+   2 |       0
+   3 |       0
+   4 |       4
+(4 rows)
+
+ROLLBACK;
+-- should be equivalent
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = 0
+WHEN NOT MATCHED THEN
+	INSERT VALUES (4, 4);
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |      10
+   2 |       0
+   3 |       0
+   4 |       4
+(4 rows)
+
+ROLLBACK;
+-- column references
+-- do a simple equivalent of an UPDATE join
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = t.balance + s.delta;
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |      10
+   2 |      25
+   3 |      50
+(3 rows)
+
+ROLLBACK;
+-- do a simple equivalent of an INSERT SELECT
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT VALUES (s.sid, s.delta);
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |      10
+   2 |      20
+   3 |      30
+   4 |      40
+(4 rows)
+
+ROLLBACK;
+-- and again with duplicate source rows
+INSERT INTO source VALUES (5, 50);
+INSERT INTO source VALUES (5, 50);
+-- do a simple equivalent of an INSERT SELECT
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+  INSERT VALUES (s.sid, s.delta);
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |      10
+   2 |      20
+   3 |      30
+   4 |      40
+   5 |      50
+   5 |      50
+(6 rows)
+
+ROLLBACK;
+-- removing duplicate source rows
+DELETE FROM source WHERE sid = 5;
+-- and again with explicitly identified column list
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT (tid, balance) VALUES (s.sid, s.delta);
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |      10
+   2 |      20
+   3 |      30
+   4 |      40
+(4 rows)
+
+ROLLBACK;
+-- and again with a subtle error: referring to non-existent target row for NOT MATCHED
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT (tid, balance) VALUES (t.tid, s.delta);
+ERROR:  invalid reference to FROM-clause entry for table "t"
+LINE 5:  INSERT (tid, balance) VALUES (t.tid, s.delta);
+                                       ^
+HINT:  There is an entry for table "t", but it cannot be referenced from this part of the query.
+-- and again with a constant ON clause
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON (SELECT true)
+WHEN NOT MATCHED THEN
+	INSERT (tid, balance) VALUES (t.tid, s.delta);
+ERROR:  invalid reference to FROM-clause entry for table "t"
+LINE 5:  INSERT (tid, balance) VALUES (t.tid, s.delta);
+                                       ^
+HINT:  There is an entry for table "t", but it cannot be referenced from this part of the query.
+SELECT * FROM target ORDER BY tid;
+ERROR:  current transaction is aborted, commands ignored until end of transaction block
+ROLLBACK;
+-- now the classic UPSERT
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = t.balance + s.delta
+WHEN NOT MATCHED THEN
+	INSERT VALUES (s.sid, s.delta);
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |      10
+   2 |      25
+   3 |      50
+   4 |      40
+(4 rows)
+
+ROLLBACK;
+-- unreachable WHEN clause should ERROR
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN /* Terminal WHEN clause for MATCHED */
+	DELETE
+WHEN MATCHED AND s.delta > 0 THEN
+	UPDATE SET balance = t.balance - s.delta;
+ERROR:  unreachable WHEN clause specified after unconditional WHEN clause
+ROLLBACK;
+-- conditional WHEN clause
+CREATE TABLE wq_target (tid integer not null, balance integer DEFAULT -1);
+CREATE TABLE wq_source (balance integer, sid integer);
+INSERT INTO wq_source (sid, balance) VALUES (1, 100);
+BEGIN;
+-- try a simple INSERT with default values first
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT (tid) VALUES (s.sid);
+SELECT * FROM wq_target;
+ tid | balance 
+-----+---------
+   1 |      -1
+(1 row)
+
+ROLLBACK;
+-- this time with a FALSE condition
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN NOT MATCHED AND FALSE THEN
+	INSERT (tid) VALUES (s.sid);
+SELECT * FROM wq_target;
+ tid | balance 
+-----+---------
+(0 rows)
+
+-- this time with an actual condition which returns false
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN NOT MATCHED AND s.balance <> 100 THEN
+	INSERT (tid) VALUES (s.sid);
+SELECT * FROM wq_target;
+ tid | balance 
+-----+---------
+(0 rows)
+
+BEGIN;
+-- and now with a condition which returns true
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN NOT MATCHED AND s.balance = 100 THEN
+	INSERT (tid) VALUES (s.sid);
+SELECT * FROM wq_target;
+ tid | balance 
+-----+---------
+   1 |      -1
+(1 row)
+
+ROLLBACK;
+-- conditions in the NOT MATCHED clause can only refer to source columns
+BEGIN;
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN NOT MATCHED AND t.balance = 100 THEN
+	INSERT (tid) VALUES (s.sid);
+ERROR:  invalid reference to FROM-clause entry for table "t"
+LINE 3: WHEN NOT MATCHED AND t.balance = 100 THEN
+                             ^
+HINT:  There is an entry for table "t", but it cannot be referenced from this part of the query.
+SELECT * FROM wq_target;
+ERROR:  current transaction is aborted, commands ignored until end of transaction block
+ROLLBACK;
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN NOT MATCHED AND s.balance = 100 THEN
+	INSERT (tid) VALUES (s.sid);
+SELECT * FROM wq_target;
+ tid | balance 
+-----+---------
+   1 |      -1
+(1 row)
+
+-- conditions in MATCHED clause can refer to both source and target
+SELECT * FROM wq_source;
+ balance | sid 
+---------+-----
+     100 |   1
+(1 row)
+
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN MATCHED AND s.balance = 100 THEN
+	UPDATE SET balance = t.balance + s.balance;
+SELECT * FROM wq_target;
+ tid | balance 
+-----+---------
+   1 |      99
+(1 row)
+
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN MATCHED AND t.balance = 100 THEN
+	UPDATE SET balance = t.balance + s.balance;
+SELECT * FROM wq_target;
+ tid | balance 
+-----+---------
+   1 |      99
+(1 row)
+
+-- check if AND works
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN MATCHED AND t.balance = 99 AND s.balance > 100 THEN
+	UPDATE SET balance = t.balance + s.balance;
+SELECT * FROM wq_target;
+ tid | balance 
+-----+---------
+   1 |      99
+(1 row)
+
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN MATCHED AND t.balance = 99 AND s.balance = 100 THEN
+	UPDATE SET balance = t.balance + s.balance;
+SELECT * FROM wq_target;
+ tid | balance 
+-----+---------
+   1 |     199
+(1 row)
+
+-- check if OR works
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN MATCHED AND t.balance = 99 OR s.balance > 100 THEN
+	UPDATE SET balance = t.balance + s.balance;
+SELECT * FROM wq_target;
+ tid | balance 
+-----+---------
+   1 |     199
+(1 row)
+
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN MATCHED AND t.balance = 199 OR s.balance > 100 THEN
+	UPDATE SET balance = t.balance + s.balance;
+SELECT * FROM wq_target;
+ tid | balance 
+-----+---------
+   1 |     299
+(1 row)
+
+-- check if subqueries work in the conditions?
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN MATCHED AND t.balance > (SELECT max(balance) FROM target) THEN
+	UPDATE SET balance = t.balance + s.balance;
+-- check if we can access system columns in the conditions
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN MATCHED AND t.xmin = t.xmax THEN
+	UPDATE SET balance = t.balance + s.balance;
+ERROR:  cannot use system column "xmin" in MERGE WHEN condition
+LINE 3: WHEN MATCHED AND t.xmin = t.xmax THEN
+                         ^
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN MATCHED AND t.tableoid >= 0 THEN
+	UPDATE SET balance = t.balance + s.balance;
+SELECT * FROM wq_target;
+ tid | balance 
+-----+---------
+   1 |     499
+(1 row)
+
+-- test preventing WHEN conditions from writing to the database
+create or replace function merge_when_and_write() returns boolean
+language plpgsql as
+$$
+BEGIN
+	INSERT INTO target VALUES (100, 100);
+	RETURN TRUE;
+END;
+$$;
+BEGIN;
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN MATCHED AND (merge_when_and_write()) THEN
+	UPDATE SET balance = t.balance + s.balance;
+ROLLBACK;
+drop function merge_when_and_write();
+DROP TABLE wq_target, wq_source;
+-- test triggers
+create or replace function merge_trigfunc () returns trigger
+language plpgsql as
+$$
+DECLARE
+	line text;
+BEGIN
+	SELECT INTO line format('%s %s %s trigger%s',
+		TG_WHEN, TG_OP, TG_LEVEL, CASE
+		WHEN TG_OP = 'INSERT' AND TG_LEVEL = 'ROW'
+			THEN format(' row: %s', NEW)
+		WHEN TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW'
+			THEN format(' row: %s -> %s', OLD, NEW)
+		WHEN TG_OP = 'DELETE' AND TG_LEVEL = 'ROW'
+			THEN format(' row: %s', OLD)
+		END);
+
+	RAISE NOTICE '%', line;
+	IF (TG_WHEN = 'BEFORE' AND TG_LEVEL = 'ROW') THEN
+		IF (TG_OP = 'DELETE') THEN
+			RETURN OLD;
+		ELSE
+			RETURN NEW;
+		END IF;
+	ELSE
+		RETURN NULL;
+	END IF;
+END;
+$$;
+CREATE TRIGGER merge_bsi BEFORE INSERT ON target FOR EACH STATEMENT EXECUTE PROCEDURE merge_trigfunc ();
+CREATE TRIGGER merge_bsu BEFORE UPDATE ON target FOR EACH STATEMENT EXECUTE PROCEDURE merge_trigfunc ();
+CREATE TRIGGER merge_bsd BEFORE DELETE ON target FOR EACH STATEMENT EXECUTE PROCEDURE merge_trigfunc ();
+CREATE TRIGGER merge_asi AFTER INSERT ON target FOR EACH STATEMENT EXECUTE PROCEDURE merge_trigfunc ();
+CREATE TRIGGER merge_asu AFTER UPDATE ON target FOR EACH STATEMENT EXECUTE PROCEDURE merge_trigfunc ();
+CREATE TRIGGER merge_asd AFTER DELETE ON target FOR EACH STATEMENT EXECUTE PROCEDURE merge_trigfunc ();
+CREATE TRIGGER merge_bri BEFORE INSERT ON target FOR EACH ROW EXECUTE PROCEDURE merge_trigfunc ();
+CREATE TRIGGER merge_bru BEFORE UPDATE ON target FOR EACH ROW EXECUTE PROCEDURE merge_trigfunc ();
+CREATE TRIGGER merge_brd BEFORE DELETE ON target FOR EACH ROW EXECUTE PROCEDURE merge_trigfunc ();
+CREATE TRIGGER merge_ari AFTER INSERT ON target FOR EACH ROW EXECUTE PROCEDURE merge_trigfunc ();
+CREATE TRIGGER merge_aru AFTER UPDATE ON target FOR EACH ROW EXECUTE PROCEDURE merge_trigfunc ();
+CREATE TRIGGER merge_ard AFTER DELETE ON target FOR EACH ROW EXECUTE PROCEDURE merge_trigfunc ();
+-- now the classic UPSERT, with a DELETE
+BEGIN;
+UPDATE target SET balance = 0 WHERE tid = 3;
+NOTICE:  BEFORE UPDATE STATEMENT trigger
+NOTICE:  BEFORE UPDATE ROW trigger row: (3,30) -> (3,0)
+NOTICE:  AFTER UPDATE ROW trigger row: (3,30) -> (3,0)
+NOTICE:  AFTER UPDATE STATEMENT trigger
+--EXPLAIN (ANALYZE ON, COSTS OFF, SUMMARY OFF, TIMING OFF)
+MERGE INTO target 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 THEN
+	INSERT VALUES (s.sid, s.delta);
+NOTICE:  BEFORE INSERT STATEMENT trigger
+NOTICE:  BEFORE UPDATE STATEMENT trigger
+NOTICE:  BEFORE DELETE STATEMENT trigger
+NOTICE:  BEFORE DELETE ROW trigger row: (3,0)
+NOTICE:  BEFORE UPDATE ROW trigger row: (2,20) -> (2,15)
+NOTICE:  BEFORE INSERT ROW trigger row: (4,40)
+NOTICE:  AFTER DELETE ROW trigger row: (3,0)
+NOTICE:  AFTER UPDATE ROW trigger row: (2,20) -> (2,15)
+NOTICE:  AFTER INSERT ROW trigger row: (4,40)
+NOTICE:  AFTER DELETE STATEMENT trigger
+NOTICE:  AFTER UPDATE STATEMENT trigger
+NOTICE:  AFTER INSERT STATEMENT trigger
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |      10
+   2 |      15
+   4 |      40
+(3 rows)
+
+ROLLBACK;
+-- Test behavior of triggers that turn UPDATE/DELETE into no-ops
+create or replace function skip_merge_op() returns trigger
+language plpgsql as
+$$
+BEGIN
+	RETURN NULL;
+END;
+$$;
+SELECT * FROM target full outer join source on (sid = tid);
+ tid | balance | sid | delta 
+-----+---------+-----+-------
+   3 |      30 |   3 |    20
+   2 |      20 |   2 |     5
+     |         |   4 |    40
+   1 |      10 |     |      
+(4 rows)
+
+create trigger merge_skip BEFORE INSERT OR UPDATE or DELETE
+  ON target FOR EACH ROW EXECUTE FUNCTION skip_merge_op();
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED AND s.sid = 3 THEN UPDATE SET balance = t.balance + s.delta
+WHEN MATCHED THEN DELETE
+WHEN NOT MATCHED THEN INSERT VALUES (sid, delta);
+NOTICE:  BEFORE INSERT STATEMENT trigger
+NOTICE:  BEFORE UPDATE STATEMENT trigger
+NOTICE:  BEFORE DELETE STATEMENT trigger
+NOTICE:  BEFORE UPDATE ROW trigger row: (3,30) -> (3,50)
+NOTICE:  BEFORE DELETE ROW trigger row: (2,20)
+NOTICE:  BEFORE INSERT ROW trigger row: (4,40)
+NOTICE:  AFTER DELETE STATEMENT trigger
+NOTICE:  AFTER UPDATE STATEMENT trigger
+NOTICE:  AFTER INSERT STATEMENT trigger
+SELECT * FROM target FULL OUTER JOIN source ON (sid = tid);
+ tid | balance | sid | delta 
+-----+---------+-----+-------
+   3 |      30 |   3 |    20
+   2 |      20 |   2 |     5
+     |         |   4 |    40
+   1 |      10 |     |      
+(4 rows)
+
+DROP TRIGGER merge_skip ON target;
+DROP FUNCTION skip_merge_op();
+-- test from PL/pgSQL
+-- make sure MERGE INTO isn't interpreted to mean returning variables like SELECT INTO
+BEGIN;
+DO LANGUAGE plpgsql $$
+BEGIN
+MERGE INTO target 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;
+END;
+$$;
+NOTICE:  BEFORE UPDATE STATEMENT trigger
+NOTICE:  BEFORE UPDATE ROW trigger row: (3,30) -> (3,10)
+NOTICE:  BEFORE UPDATE ROW trigger row: (2,20) -> (2,15)
+NOTICE:  AFTER UPDATE ROW trigger row: (3,30) -> (3,10)
+NOTICE:  AFTER UPDATE ROW trigger row: (2,20) -> (2,15)
+NOTICE:  AFTER UPDATE STATEMENT trigger
+ROLLBACK;
+--source constants
+BEGIN;
+MERGE INTO target t
+USING (SELECT 9 AS sid, 57 AS delta) AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT (tid, balance) VALUES (s.sid, s.delta);
+NOTICE:  BEFORE INSERT STATEMENT trigger
+NOTICE:  BEFORE INSERT ROW trigger row: (9,57)
+NOTICE:  AFTER INSERT ROW trigger row: (9,57)
+NOTICE:  AFTER INSERT STATEMENT trigger
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |      10
+   2 |      20
+   3 |      30
+   9 |      57
+(4 rows)
+
+ROLLBACK;
+--source query
+BEGIN;
+MERGE INTO target t
+USING (SELECT sid, delta FROM source WHERE delta > 0) AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT (tid, balance) VALUES (s.sid, s.delta);
+NOTICE:  BEFORE INSERT STATEMENT trigger
+NOTICE:  BEFORE INSERT ROW trigger row: (4,40)
+NOTICE:  AFTER INSERT ROW trigger row: (4,40)
+NOTICE:  AFTER INSERT STATEMENT trigger
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |      10
+   2 |      20
+   3 |      30
+   4 |      40
+(4 rows)
+
+ROLLBACK;
+BEGIN;
+MERGE INTO target t
+USING (SELECT sid, delta as newname FROM source WHERE delta > 0) AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT (tid, balance) VALUES (s.sid, s.newname);
+NOTICE:  BEFORE INSERT STATEMENT trigger
+NOTICE:  BEFORE INSERT ROW trigger row: (4,40)
+NOTICE:  AFTER INSERT ROW trigger row: (4,40)
+NOTICE:  AFTER INSERT STATEMENT trigger
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |      10
+   2 |      20
+   3 |      30
+   4 |      40
+(4 rows)
+
+ROLLBACK;
+--self-merge
+BEGIN;
+MERGE INTO target t1
+USING target t2
+ON t1.tid = t2.tid
+WHEN MATCHED THEN
+	UPDATE SET balance = t1.balance + t2.balance
+WHEN NOT MATCHED THEN
+	INSERT VALUES (t2.tid, t2.balance);
+NOTICE:  BEFORE INSERT STATEMENT trigger
+NOTICE:  BEFORE UPDATE STATEMENT trigger
+NOTICE:  BEFORE UPDATE ROW trigger row: (1,10) -> (1,20)
+NOTICE:  BEFORE UPDATE ROW trigger row: (2,20) -> (2,40)
+NOTICE:  BEFORE UPDATE ROW trigger row: (3,30) -> (3,60)
+NOTICE:  AFTER UPDATE ROW trigger row: (1,10) -> (1,20)
+NOTICE:  AFTER UPDATE ROW trigger row: (2,20) -> (2,40)
+NOTICE:  AFTER UPDATE ROW trigger row: (3,30) -> (3,60)
+NOTICE:  AFTER UPDATE STATEMENT trigger
+NOTICE:  AFTER INSERT STATEMENT trigger
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |      20
+   2 |      40
+   3 |      60
+(3 rows)
+
+ROLLBACK;
+BEGIN;
+MERGE INTO target t
+USING (SELECT tid as sid, balance as delta FROM target WHERE balance > 0) AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT (tid, balance) VALUES (s.sid, s.delta);
+NOTICE:  BEFORE INSERT STATEMENT trigger
+NOTICE:  AFTER INSERT STATEMENT trigger
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |      10
+   2 |      20
+   3 |      30
+(3 rows)
+
+ROLLBACK;
+BEGIN;
+MERGE INTO target t
+USING
+(SELECT sid, max(delta) AS delta
+ FROM source
+ GROUP BY sid
+ HAVING count(*) = 1
+ ORDER BY sid ASC) AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT (tid, balance) VALUES (s.sid, s.delta);
+NOTICE:  BEFORE INSERT STATEMENT trigger
+NOTICE:  BEFORE INSERT ROW trigger row: (4,40)
+NOTICE:  AFTER INSERT ROW trigger row: (4,40)
+NOTICE:  AFTER INSERT STATEMENT trigger
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |      10
+   2 |      20
+   3 |      30
+   4 |      40
+(4 rows)
+
+ROLLBACK;
+-- plpgsql parameters and results
+BEGIN;
+CREATE FUNCTION merge_func (p_id integer, p_bal integer)
+RETURNS INTEGER
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ result integer;
+BEGIN
+MERGE INTO target t
+USING (SELECT p_id AS sid) AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = t.balance - p_bal;
+IF FOUND THEN
+	GET DIAGNOSTICS result := ROW_COUNT;
+END IF;
+RETURN result;
+END;
+$$;
+SELECT merge_func(3, 4);
+NOTICE:  BEFORE UPDATE STATEMENT trigger
+NOTICE:  BEFORE UPDATE ROW trigger row: (3,30) -> (3,26)
+NOTICE:  AFTER UPDATE ROW trigger row: (3,30) -> (3,26)
+NOTICE:  AFTER UPDATE STATEMENT trigger
+ merge_func 
+------------
+          1
+(1 row)
+
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |      10
+   2 |      20
+   3 |      26
+(3 rows)
+
+ROLLBACK;
+-- PREPARE
+BEGIN;
+prepare foom as merge into target t using (select 1 as sid) s on (t.tid = s.sid) when matched then update set balance = 1;
+execute foom;
+NOTICE:  BEFORE UPDATE STATEMENT trigger
+NOTICE:  BEFORE UPDATE ROW trigger row: (1,10) -> (1,1)
+NOTICE:  AFTER UPDATE ROW trigger row: (1,10) -> (1,1)
+NOTICE:  AFTER UPDATE STATEMENT trigger
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |       1
+   2 |      20
+   3 |      30
+(3 rows)
+
+ROLLBACK;
+BEGIN;
+PREPARE foom2 (integer, integer) AS
+MERGE INTO target t
+USING (SELECT 1) s
+ON t.tid = $1
+WHEN MATCHED THEN
+UPDATE SET balance = $2;
+--EXPLAIN (ANALYZE ON, COSTS OFF, SUMMARY OFF, TIMING OFF)
+execute foom2 (1, 1);
+NOTICE:  BEFORE UPDATE STATEMENT trigger
+NOTICE:  BEFORE UPDATE ROW trigger row: (1,10) -> (1,1)
+NOTICE:  AFTER UPDATE ROW trigger row: (1,10) -> (1,1)
+NOTICE:  AFTER UPDATE STATEMENT trigger
+SELECT * FROM target ORDER BY tid;
+ tid | balance 
+-----+---------
+   1 |       1
+   2 |      20
+   3 |      30
+(3 rows)
+
+ROLLBACK;
+-- subqueries in source relation
+CREATE TABLE sq_target (tid integer NOT NULL, balance integer);
+CREATE TABLE sq_source (delta integer, sid integer, balance integer DEFAULT 0);
+INSERT INTO sq_target(tid, balance) VALUES (1,100), (2,200), (3,300);
+INSERT INTO sq_source(sid, delta) VALUES (1,10), (2,20), (4,40);
+BEGIN;
+MERGE INTO sq_target t
+USING (SELECT * FROM sq_source) s
+ON tid = sid
+WHEN MATCHED AND t.balance > delta THEN
+	UPDATE SET balance = t.balance + delta;
+SELECT * FROM sq_target;
+ tid | balance 
+-----+---------
+   3 |     300
+   1 |     110
+   2 |     220
+(3 rows)
+
+ROLLBACK;
+-- try a view
+CREATE VIEW v AS SELECT * FROM sq_source WHERE sid < 2;
+BEGIN;
+MERGE INTO sq_target
+USING v
+ON tid = sid
+WHEN MATCHED THEN
+    UPDATE SET balance = v.balance + delta;
+SELECT * FROM sq_target;
+ tid | balance 
+-----+---------
+   2 |     200
+   3 |     300
+   1 |      10
+(3 rows)
+
+ROLLBACK;
+-- ambiguous reference to a column
+BEGIN;
+MERGE INTO sq_target
+USING v
+ON tid = sid
+WHEN MATCHED AND tid > 2 THEN
+    UPDATE SET balance = balance + delta
+WHEN NOT MATCHED THEN
+	INSERT (balance, tid) VALUES (balance + delta, sid)
+WHEN MATCHED AND tid < 2 THEN
+	DELETE;
+ERROR:  column reference "balance" is ambiguous
+LINE 5:     UPDATE SET balance = balance + delta
+                                 ^
+ROLLBACK;
+BEGIN;
+INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10);
+MERGE INTO sq_target t
+USING v
+ON tid = sid
+WHEN MATCHED AND tid > 2 THEN
+    UPDATE SET balance = t.balance + delta
+WHEN NOT MATCHED THEN
+	INSERT (balance, tid) VALUES (balance + delta, sid)
+WHEN MATCHED AND tid < 2 THEN
+	DELETE;
+SELECT * FROM sq_target;
+ tid | balance 
+-----+---------
+   2 |     200
+   3 |     300
+  -1 |     -11
+(3 rows)
+
+ROLLBACK;
+-- CTEs
+BEGIN;
+INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10);
+WITH targq AS (
+	SELECT * FROM v
+)
+MERGE INTO sq_target t
+USING v
+ON tid = sid
+WHEN MATCHED AND tid > 2 THEN
+    UPDATE SET balance = t.balance + delta
+WHEN NOT MATCHED THEN
+	INSERT (balance, tid) VALUES (balance + delta, sid)
+WHEN MATCHED AND tid < 2 THEN
+	DELETE;
+ROLLBACK;
+-- RETURNING
+BEGIN;
+INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10);
+MERGE INTO sq_target t
+USING v
+ON tid = sid
+WHEN MATCHED AND tid > 2 THEN
+    UPDATE SET balance = t.balance + delta
+WHEN NOT MATCHED THEN
+	INSERT (balance, tid) VALUES (balance + delta, sid)
+WHEN MATCHED AND tid < 2 THEN
+	DELETE
+RETURNING *;
+ERROR:  syntax error at or near "RETURNING"
+LINE 10: RETURNING *;
+         ^
+ROLLBACK;
+-- EXPLAIN
+CREATE TABLE ex_mtarget (a int, b int);
+CREATE TABLE ex_msource (a int, b int);
+INSERT INTO ex_mtarget SELECT i, i*10 FROM generate_series(1,100,2) i;
+INSERT INTO ex_msource SELECT i, i*10 FROM generate_series(1,100,1) i;
+CREATE FUNCTION explain_merge(query text) RETURNS SETOF text
+LANGUAGE plpgsql AS
+$$
+DECLARE ln text;
+BEGIN
+    FOR ln IN
+        EXECUTE 'explain (analyze, timing off, summary off, costs off) ' ||
+		  query
+    LOOP
+        ln := regexp_replace(ln, 'Memory: \S*',  'Memory: xxx');
+        RETURN NEXT ln;
+    END LOOP;
+END;
+$$;
+-- only updates
+SELECT explain_merge('
+MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
+WHEN MATCHED THEN
+	UPDATE SET b = t.b + 1');
+                            explain_merge                             
+----------------------------------------------------------------------
+ Merge on ex_mtarget t (actual rows=0 loops=1)
+   Tuples Inserted: 0
+   Tuples Updated: 50
+   Tuples Deleted: 0
+   Tuples Skipped: 0
+   ->  Merge Join (actual rows=50 loops=1)
+         Merge Cond: (t.a = s.a)
+         ->  Sort (actual rows=50 loops=1)
+               Sort Key: t.a
+               Sort Method: quicksort  Memory: xxx
+               ->  Seq Scan on ex_mtarget t (actual rows=50 loops=1)
+         ->  Sort (actual rows=100 loops=1)
+               Sort Key: s.a
+               Sort Method: quicksort  Memory: xxx
+               ->  Seq Scan on ex_msource s (actual rows=100 loops=1)
+(15 rows)
+
+-- only updates to selected tuples
+SELECT explain_merge('
+MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
+WHEN MATCHED AND t.a < 10 THEN
+	UPDATE SET b = t.b + 1');
+                            explain_merge                             
+----------------------------------------------------------------------
+ Merge on ex_mtarget t (actual rows=0 loops=1)
+   Tuples Inserted: 0
+   Tuples Updated: 5
+   Tuples Deleted: 0
+   Tuples Skipped: 45
+   ->  Merge Join (actual rows=50 loops=1)
+         Merge Cond: (t.a = s.a)
+         ->  Sort (actual rows=50 loops=1)
+               Sort Key: t.a
+               Sort Method: quicksort  Memory: xxx
+               ->  Seq Scan on ex_mtarget t (actual rows=50 loops=1)
+         ->  Sort (actual rows=100 loops=1)
+               Sort Key: s.a
+               Sort Method: quicksort  Memory: xxx
+               ->  Seq Scan on ex_msource s (actual rows=100 loops=1)
+(15 rows)
+
+-- updates + deletes
+SELECT explain_merge('
+MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
+WHEN MATCHED AND t.a < 10 THEN
+	UPDATE SET b = t.b + 1
+WHEN MATCHED AND t.a >= 10 AND t.a <= 20 THEN
+	DELETE');
+                            explain_merge                             
+----------------------------------------------------------------------
+ Merge on ex_mtarget t (actual rows=0 loops=1)
+   Tuples Inserted: 0
+   Tuples Updated: 5
+   Tuples Deleted: 5
+   Tuples Skipped: 40
+   ->  Merge Join (actual rows=50 loops=1)
+         Merge Cond: (t.a = s.a)
+         ->  Sort (actual rows=50 loops=1)
+               Sort Key: t.a
+               Sort Method: quicksort  Memory: xxx
+               ->  Seq Scan on ex_mtarget t (actual rows=50 loops=1)
+         ->  Sort (actual rows=100 loops=1)
+               Sort Key: s.a
+               Sort Method: quicksort  Memory: xxx
+               ->  Seq Scan on ex_msource s (actual rows=100 loops=1)
+(15 rows)
+
+-- only inserts
+SELECT explain_merge('
+MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
+WHEN NOT MATCHED AND s.a < 10 THEN
+	INSERT VALUES (a, b)');
+                            explain_merge                             
+----------------------------------------------------------------------
+ Merge on ex_mtarget t (actual rows=0 loops=1)
+   Tuples Inserted: 4
+   Tuples Updated: 0
+   Tuples Deleted: 0
+   Tuples Skipped: 96
+   ->  Merge Left Join (actual rows=100 loops=1)
+         Merge Cond: (s.a = t.a)
+         ->  Sort (actual rows=100 loops=1)
+               Sort Key: s.a
+               Sort Method: quicksort  Memory: xxx
+               ->  Seq Scan on ex_msource s (actual rows=100 loops=1)
+         ->  Sort (actual rows=45 loops=1)
+               Sort Key: t.a
+               Sort Method: quicksort  Memory: xxx
+               ->  Seq Scan on ex_mtarget t (actual rows=45 loops=1)
+(15 rows)
+
+-- all three
+SELECT explain_merge('
+MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
+WHEN MATCHED AND t.a < 10 THEN
+	UPDATE SET b = t.b + 1
+WHEN MATCHED AND t.a >= 30 AND t.a <= 40 THEN
+	DELETE
+WHEN NOT MATCHED AND s.a < 20 THEN
+	INSERT VALUES (a, b)');
+                            explain_merge                             
+----------------------------------------------------------------------
+ Merge on ex_mtarget t (actual rows=0 loops=1)
+   Tuples Inserted: 10
+   Tuples Updated: 9
+   Tuples Deleted: 5
+   Tuples Skipped: 76
+   ->  Merge Left Join (actual rows=100 loops=1)
+         Merge Cond: (s.a = t.a)
+         ->  Sort (actual rows=100 loops=1)
+               Sort Key: s.a
+               Sort Method: quicksort  Memory: xxx
+               ->  Seq Scan on ex_msource s (actual rows=100 loops=1)
+         ->  Sort (actual rows=49 loops=1)
+               Sort Key: t.a
+               Sort Method: quicksort  Memory: xxx
+               ->  Seq Scan on ex_mtarget t (actual rows=49 loops=1)
+(15 rows)
+
+DROP TABLE ex_msource, ex_mtarget;
+DROP FUNCTION explain_merge(text);
+-- Subqueries
+BEGIN;
+MERGE INTO sq_target t
+USING v
+ON tid = sid
+WHEN MATCHED THEN
+    UPDATE SET balance = (SELECT count(*) FROM sq_target);
+SELECT * FROM sq_target WHERE tid = 1;
+ tid | balance 
+-----+---------
+   1 |       3
+(1 row)
+
+ROLLBACK;
+BEGIN;
+MERGE INTO sq_target t
+USING v
+ON tid = sid
+WHEN MATCHED AND (SELECT count(*) > 0 FROM sq_target) THEN
+    UPDATE SET balance = 42;
+SELECT * FROM sq_target WHERE tid = 1;
+ tid | balance 
+-----+---------
+   1 |      42
+(1 row)
+
+ROLLBACK;
+BEGIN;
+MERGE INTO sq_target t
+USING v
+ON tid = sid AND (SELECT count(*) > 0 FROM sq_target)
+WHEN MATCHED THEN
+    UPDATE SET balance = 42;
+SELECT * FROM sq_target WHERE tid = 1;
+ tid | balance 
+-----+---------
+   1 |      42
+(1 row)
+
+ROLLBACK;
+DROP TABLE sq_target, sq_source CASCADE;
+NOTICE:  drop cascades to view v
+CREATE TABLE pa_target (tid integer, balance float, val text)
+	PARTITION BY LIST (tid);
+CREATE TABLE part1 PARTITION OF pa_target FOR VALUES IN (1,4);
+CREATE TABLE part2 PARTITION OF pa_target FOR VALUES IN (2,5,6);
+CREATE TABLE part3 PARTITION OF pa_target FOR VALUES IN (3,8,9);
+CREATE TABLE part4 PARTITION OF pa_target DEFAULT;
+CREATE TABLE pa_source (sid integer, delta float);
+-- insert many rows to the source table
+INSERT INTO pa_source SELECT id, id * 10  FROM generate_series(1,14) AS id;
+-- insert a few rows in the target table (odd numbered tid)
+INSERT INTO pa_target SELECT id, id * 100, 'initial' FROM generate_series(1,14,2) AS id;
+-- try simple MERGE
+BEGIN;
+MERGE INTO pa_target t
+  USING pa_source s
+  ON t.tid = s.sid
+  WHEN MATCHED THEN
+    UPDATE SET balance = balance + delta, val = val || ' updated by merge'
+  WHEN NOT MATCHED THEN
+    INSERT VALUES (sid, delta, 'inserted by merge');
+SELECT * FROM pa_target ORDER BY tid;
+ tid | balance |           val            
+-----+---------+--------------------------
+   1 |     110 | initial updated by merge
+   2 |      20 | inserted by merge
+   3 |     330 | initial updated by merge
+   4 |      40 | inserted by merge
+   5 |     550 | initial updated by merge
+   6 |      60 | inserted by merge
+   7 |     770 | initial updated by merge
+   8 |      80 | inserted by merge
+   9 |     990 | initial updated by merge
+  10 |     100 | inserted by merge
+  11 |    1210 | initial updated by merge
+  12 |     120 | inserted by merge
+  13 |    1430 | initial updated by merge
+  14 |     140 | inserted by merge
+(14 rows)
+
+ROLLBACK;
+-- same with a constant qual
+BEGIN;
+MERGE INTO pa_target t
+  USING pa_source s
+  ON t.tid = s.sid AND tid = 1
+  WHEN MATCHED THEN
+    UPDATE SET balance = balance + delta, val = val || ' updated by merge'
+  WHEN NOT MATCHED THEN
+    INSERT VALUES (sid, delta, 'inserted by merge');
+SELECT * FROM pa_target ORDER BY tid;
+ tid | balance |           val            
+-----+---------+--------------------------
+   1 |     110 | initial updated by merge
+   2 |      20 | inserted by merge
+   3 |      30 | inserted by merge
+   3 |     300 | initial
+   4 |      40 | inserted by merge
+   5 |     500 | initial
+   5 |      50 | inserted by merge
+   6 |      60 | inserted by merge
+   7 |     700 | initial
+   7 |      70 | inserted by merge
+   8 |      80 | inserted by merge
+   9 |      90 | inserted by merge
+   9 |     900 | initial
+  10 |     100 | inserted by merge
+  11 |    1100 | initial
+  11 |     110 | inserted by merge
+  12 |     120 | inserted by merge
+  13 |    1300 | initial
+  13 |     130 | inserted by merge
+  14 |     140 | inserted by merge
+(20 rows)
+
+ROLLBACK;
+-- try updating the partition key column
+BEGIN;
+MERGE INTO pa_target t
+  USING pa_source s
+  ON t.tid = s.sid
+  WHEN MATCHED THEN
+    UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge'
+  WHEN NOT MATCHED THEN
+    INSERT VALUES (sid, delta, 'inserted by merge');
+SELECT * FROM pa_target ORDER BY tid;
+ tid | balance |           val            
+-----+---------+--------------------------
+   2 |     110 | initial updated by merge
+   2 |      20 | inserted by merge
+   4 |      40 | inserted by merge
+   4 |     330 | initial updated by merge
+   6 |     550 | initial updated by merge
+   6 |      60 | inserted by merge
+   8 |      80 | inserted by merge
+   8 |     770 | initial updated by merge
+  10 |     990 | initial updated by merge
+  10 |     100 | inserted by merge
+  12 |    1210 | initial updated by merge
+  12 |     120 | inserted by merge
+  14 |    1430 | initial updated by merge
+  14 |     140 | inserted by merge
+(14 rows)
+
+ROLLBACK;
+DROP TABLE pa_target CASCADE;
+-- The target table is partitioned in the same way, but this time by attaching
+-- partitions which have columns in different order, dropped columns etc.
+CREATE TABLE pa_target (tid integer, balance float, val text)
+	PARTITION BY LIST (tid);
+CREATE TABLE part1 (tid integer, balance float, val text);
+CREATE TABLE part2 (balance float, tid integer, val text);
+CREATE TABLE part3 (tid integer, balance float, val text);
+CREATE TABLE part4 (extraid text, tid integer, balance float, val text);
+ALTER TABLE part4 DROP COLUMN extraid;
+ALTER TABLE pa_target ATTACH PARTITION part1 FOR VALUES IN (1,4);
+ALTER TABLE pa_target ATTACH PARTITION part2 FOR VALUES IN (2,5,6);
+ALTER TABLE pa_target ATTACH PARTITION part3 FOR VALUES IN (3,8,9);
+ALTER TABLE pa_target ATTACH PARTITION part4 DEFAULT;
+-- insert a few rows in the target table (odd numbered tid)
+INSERT INTO pa_target SELECT id, id * 100, 'initial' FROM generate_series(1,14,2) AS id;
+-- try simple MERGE
+BEGIN;
+MERGE INTO pa_target t
+  USING pa_source s
+  ON t.tid = s.sid
+  WHEN MATCHED THEN
+    UPDATE SET balance = balance + delta, val = val || ' updated by merge'
+  WHEN NOT MATCHED THEN
+    INSERT VALUES (sid, delta, 'inserted by merge');
+SELECT * FROM pa_target ORDER BY tid;
+ tid | balance |           val            
+-----+---------+--------------------------
+   1 |     110 | initial updated by merge
+   2 |      20 | inserted by merge
+   3 |     330 | initial updated by merge
+   4 |      40 | inserted by merge
+   5 |     550 | initial updated by merge
+   6 |      60 | inserted by merge
+   7 |     770 | initial updated by merge
+   8 |      80 | inserted by merge
+   9 |     990 | initial updated by merge
+  10 |     100 | inserted by merge
+  11 |    1210 | initial updated by merge
+  12 |     120 | inserted by merge
+  13 |    1430 | initial updated by merge
+  14 |     140 | inserted by merge
+(14 rows)
+
+ROLLBACK;
+-- same with a constant qual
+BEGIN;
+MERGE INTO pa_target t
+  USING pa_source s
+  ON t.tid = s.sid AND tid IN (1, 5)
+  WHEN MATCHED AND tid % 5 = 0 THEN DELETE
+  WHEN MATCHED THEN
+    UPDATE SET balance = balance + delta, val = val || ' updated by merge'
+  WHEN NOT MATCHED THEN
+    INSERT VALUES (sid, delta, 'inserted by merge');
+SELECT * FROM pa_target ORDER BY tid;
+ tid | balance |           val            
+-----+---------+--------------------------
+   1 |     110 | initial updated by merge
+   2 |      20 | inserted by merge
+   3 |      30 | inserted by merge
+   3 |     300 | initial
+   4 |      40 | inserted by merge
+   6 |      60 | inserted by merge
+   7 |     700 | initial
+   7 |      70 | inserted by merge
+   8 |      80 | inserted by merge
+   9 |     900 | initial
+   9 |      90 | inserted by merge
+  10 |     100 | inserted by merge
+  11 |     110 | inserted by merge
+  11 |    1100 | initial
+  12 |     120 | inserted by merge
+  13 |    1300 | initial
+  13 |     130 | inserted by merge
+  14 |     140 | inserted by merge
+(18 rows)
+
+ROLLBACK;
+-- try updating the partition key column
+BEGIN;
+MERGE INTO pa_target t
+  USING pa_source s
+  ON t.tid = s.sid
+  WHEN MATCHED THEN
+    UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge'
+  WHEN NOT MATCHED THEN
+    INSERT VALUES (sid, delta, 'inserted by merge');
+SELECT * FROM pa_target ORDER BY tid;
+ tid | balance |           val            
+-----+---------+--------------------------
+   2 |     110 | initial updated by merge
+   2 |      20 | inserted by merge
+   4 |      40 | inserted by merge
+   4 |     330 | initial updated by merge
+   6 |     550 | initial updated by merge
+   6 |      60 | inserted by merge
+   8 |      80 | inserted by merge
+   8 |     770 | initial updated by merge
+  10 |     990 | initial updated by merge
+  10 |     100 | inserted by merge
+  12 |    1210 | initial updated by merge
+  12 |     120 | inserted by merge
+  14 |    1430 | initial updated by merge
+  14 |     140 | inserted by merge
+(14 rows)
+
+ROLLBACK;
+DROP TABLE pa_source;
+DROP TABLE pa_target CASCADE;
+-- Sub-partitioning
+CREATE TABLE pa_target (logts timestamp, tid integer, balance float, val text)
+	PARTITION BY RANGE (logts);
+CREATE TABLE part_m01 PARTITION OF pa_target
+	FOR VALUES FROM ('2017-01-01') TO ('2017-02-01')
+	PARTITION BY LIST (tid);
+CREATE TABLE part_m01_odd PARTITION OF part_m01
+	FOR VALUES IN (1,3,5,7,9);
+CREATE TABLE part_m01_even PARTITION OF part_m01
+	FOR VALUES IN (2,4,6,8);
+CREATE TABLE part_m02 PARTITION OF pa_target
+	FOR VALUES FROM ('2017-02-01') TO ('2017-03-01')
+	PARTITION BY LIST (tid);
+CREATE TABLE part_m02_odd PARTITION OF part_m02
+	FOR VALUES IN (1,3,5,7,9);
+CREATE TABLE part_m02_even PARTITION OF part_m02
+	FOR VALUES IN (2,4,6,8);
+CREATE TABLE pa_source (sid integer, delta float);
+-- insert many rows to the source table
+INSERT INTO pa_source SELECT id, id * 10  FROM generate_series(1,14) AS id;
+-- insert a few rows in the target table (odd numbered tid)
+INSERT INTO pa_target SELECT '2017-01-31', id, id * 100, 'initial' FROM generate_series(1,9,3) AS id;
+INSERT INTO pa_target SELECT '2017-02-28', id, id * 100, 'initial' FROM generate_series(2,9,3) AS id;
+-- try simple MERGE
+BEGIN;
+MERGE INTO pa_target t
+  USING (SELECT '2017-01-15' AS slogts, * FROM pa_source WHERE sid < 10) s
+  ON t.tid = s.sid
+  WHEN MATCHED THEN
+    UPDATE SET balance = balance + delta, val = val || ' updated by merge'
+  WHEN NOT MATCHED THEN
+    INSERT VALUES (slogts::timestamp, sid, delta, 'inserted by merge');
+SELECT * FROM pa_target ORDER BY tid;
+          logts           | tid | balance |           val            
+--------------------------+-----+---------+--------------------------
+ Tue Jan 31 00:00:00 2017 |   1 |     110 | initial updated by merge
+ Tue Feb 28 00:00:00 2017 |   2 |     220 | initial updated by merge
+ Sun Jan 15 00:00:00 2017 |   3 |      30 | inserted by merge
+ Tue Jan 31 00:00:00 2017 |   4 |     440 | initial updated by merge
+ Tue Feb 28 00:00:00 2017 |   5 |     550 | initial updated by merge
+ Sun Jan 15 00:00:00 2017 |   6 |      60 | inserted by merge
+ Tue Jan 31 00:00:00 2017 |   7 |     770 | initial updated by merge
+ Tue Feb 28 00:00:00 2017 |   8 |     880 | initial updated by merge
+ Sun Jan 15 00:00:00 2017 |   9 |      90 | inserted by merge
+(9 rows)
+
+ROLLBACK;
+DROP TABLE pa_source;
+DROP TABLE pa_target CASCADE;
+-- some complex joins on the source side
+CREATE TABLE cj_target (tid integer, balance float, val text);
+CREATE TABLE cj_source1 (sid1 integer, scat integer, delta integer);
+CREATE TABLE cj_source2 (sid2 integer, sval text);
+INSERT INTO cj_source1 VALUES (1, 10, 100);
+INSERT INTO cj_source1 VALUES (1, 20, 200);
+INSERT INTO cj_source1 VALUES (2, 20, 300);
+INSERT INTO cj_source1 VALUES (3, 10, 400);
+INSERT INTO cj_source2 VALUES (1, 'initial source2');
+INSERT INTO cj_source2 VALUES (2, 'initial source2');
+INSERT INTO cj_source2 VALUES (3, 'initial source2');
+-- source relation is an unaliased join
+MERGE INTO cj_target t
+USING cj_source1 s1
+	INNER JOIN cj_source2 s2 ON sid1 = sid2
+ON t.tid = sid1
+WHEN NOT MATCHED THEN
+	INSERT VALUES (sid1, delta, sval);
+-- try accessing columns from either side of the source join
+MERGE INTO cj_target t
+USING cj_source2 s2
+	INNER JOIN cj_source1 s1 ON sid1 = sid2 AND scat = 20
+ON t.tid = sid1
+WHEN NOT MATCHED THEN
+	INSERT VALUES (sid2, delta, sval)
+WHEN MATCHED THEN
+	DELETE;
+-- some simple expressions in INSERT targetlist
+MERGE INTO cj_target t
+USING cj_source2 s2
+	INNER JOIN cj_source1 s1 ON sid1 = sid2
+ON t.tid = sid1
+WHEN NOT MATCHED THEN
+	INSERT VALUES (sid2, delta + scat, sval)
+WHEN MATCHED THEN
+	UPDATE SET val = val || ' updated by merge';
+MERGE INTO cj_target t
+USING cj_source2 s2
+	INNER JOIN cj_source1 s1 ON sid1 = sid2 AND scat = 20
+ON t.tid = sid1
+WHEN MATCHED THEN
+	UPDATE SET val = val || ' ' || delta::text;
+SELECT * FROM cj_target;
+ tid | balance |               val                
+-----+---------+----------------------------------
+   3 |     400 | initial source2 updated by merge
+   1 |     220 | initial source2 200
+   1 |     110 | initial source2 200
+   2 |     320 | initial source2 300
+(4 rows)
+
+ALTER TABLE cj_source1 RENAME COLUMN sid1 TO sid;
+ALTER TABLE cj_source2 RENAME COLUMN sid2 TO sid;
+TRUNCATE cj_target;
+MERGE INTO cj_target t
+USING cj_source1 s1
+	INNER JOIN cj_source2 s2 ON s1.sid = s2.sid
+ON t.tid = s1.sid
+WHEN NOT MATCHED THEN
+	INSERT VALUES (s2.sid, delta, sval);
+DROP TABLE cj_source2, cj_source1, cj_target;
+-- Function scans
+CREATE TABLE fs_target (a int, b int, c text);
+MERGE INTO fs_target t
+USING generate_series(1,100,1) AS id
+ON t.a = id
+WHEN MATCHED THEN
+	UPDATE SET b = b + id
+WHEN NOT MATCHED THEN
+	INSERT VALUES (id, -1);
+MERGE INTO fs_target t
+USING generate_series(1,100,2) AS id
+ON t.a = id
+WHEN MATCHED THEN
+	UPDATE SET b = b + id, c = 'updated '|| id.*::text
+WHEN NOT MATCHED THEN
+	INSERT VALUES (id, -1, 'inserted ' || id.*::text);
+SELECT count(*) FROM fs_target;
+ count 
+-------
+   100
+(1 row)
+
+DROP TABLE fs_target;
+-- SERIALIZABLE test
+-- handled in isolation tests
+-- Inheritance-based partitioning
+CREATE TABLE measurement (
+    city_id         int not null,
+    logdate         date not null,
+    peaktemp        int,
+    unitsales       int
+);
+CREATE TABLE measurement_y2006m02 (
+    CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
+) INHERITS (measurement);
+CREATE TABLE measurement_y2006m03 (
+    CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
+) INHERITS (measurement);
+CREATE TABLE measurement_y2007m01 (
+    filler          text,
+    peaktemp        int,
+    logdate         date not null,
+    city_id         int not null,
+    unitsales       int
+    CHECK ( logdate >= DATE '2007-01-01' AND logdate < DATE '2007-02-01')
+);
+ALTER TABLE measurement_y2007m01 DROP COLUMN filler;
+ALTER TABLE measurement_y2007m01 INHERIT measurement;
+CREATE OR REPLACE FUNCTION measurement_insert_trigger()
+RETURNS TRIGGER AS $$
+BEGIN
+    IF ( NEW.logdate >= DATE '2006-02-01' AND
+         NEW.logdate < DATE '2006-03-01' ) THEN
+        INSERT INTO measurement_y2006m02 VALUES (NEW.*);
+    ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
+            NEW.logdate < DATE '2006-04-01' ) THEN
+        INSERT INTO measurement_y2006m03 VALUES (NEW.*);
+    ELSIF ( NEW.logdate >= DATE '2007-01-01' AND
+            NEW.logdate < DATE '2007-02-01' ) THEN
+        INSERT INTO measurement_y2007m01 (city_id, logdate, peaktemp, unitsales)
+            VALUES (NEW.*);
+    ELSE
+        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
+    END IF;
+    RETURN NULL;
+END;
+$$ LANGUAGE plpgsql ;
+CREATE TRIGGER insert_measurement_trigger
+    BEFORE INSERT ON measurement
+    FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();
+INSERT INTO measurement VALUES (1, '2006-02-10', 35, 10);
+INSERT INTO measurement VALUES (1, '2006-02-16', 45, 20);
+INSERT INTO measurement VALUES (1, '2006-03-17', 25, 10);
+INSERT INTO measurement VALUES (1, '2006-03-27', 15, 40);
+INSERT INTO measurement VALUES (1, '2007-01-15', 10, 10);
+INSERT INTO measurement VALUES (1, '2007-01-17', 10, 10);
+SELECT tableoid::regclass, * FROM measurement ORDER BY city_id, logdate;
+       tableoid       | city_id |  logdate   | peaktemp | unitsales 
+----------------------+---------+------------+----------+-----------
+ measurement_y2006m02 |       1 | 02-10-2006 |       35 |        10
+ measurement_y2006m02 |       1 | 02-16-2006 |       45 |        20
+ measurement_y2006m03 |       1 | 03-17-2006 |       25 |        10
+ measurement_y2006m03 |       1 | 03-27-2006 |       15 |        40
+ measurement_y2007m01 |       1 | 01-15-2007 |       10 |        10
+ measurement_y2007m01 |       1 | 01-17-2007 |       10 |        10
+(6 rows)
+
+CREATE TABLE new_measurement (LIKE measurement);
+INSERT INTO new_measurement VALUES (1, '2006-03-01', 20, 10);
+INSERT INTO new_measurement VALUES (1, '2006-02-16', 50, 10);
+INSERT INTO new_measurement VALUES (2, '2006-02-10', 20, 20);
+INSERT INTO new_measurement VALUES (1, '2006-03-27', NULL, NULL);
+INSERT INTO new_measurement VALUES (1, '2007-01-17', NULL, NULL);
+INSERT INTO new_measurement VALUES (1, '2007-01-15', 5, NULL);
+INSERT INTO new_measurement VALUES (1, '2007-01-16', 10, 10);
+MERGE into measurement m
+ USING new_measurement nm ON
+      (m.city_id = nm.city_id and m.logdate=nm.logdate)
+WHEN MATCHED AND nm.peaktemp IS NULL THEN DELETE
+WHEN MATCHED THEN UPDATE
+     SET peaktemp = greatest(m.peaktemp, nm.peaktemp),
+        unitsales = m.unitsales + coalesce(nm.unitsales, 0)
+WHEN NOT MATCHED THEN INSERT
+     (city_id, logdate, peaktemp, unitsales)
+   VALUES (city_id, logdate, peaktemp, unitsales);
+SELECT tableoid::regclass, * FROM measurement ORDER BY city_id, logdate;
+       tableoid       | city_id |  logdate   | peaktemp | unitsales 
+----------------------+---------+------------+----------+-----------
+ measurement_y2006m02 |       1 | 02-10-2006 |       35 |        10
+ measurement_y2006m02 |       1 | 02-16-2006 |       50 |        30
+ measurement_y2006m03 |       1 | 03-01-2006 |       20 |        10
+ measurement_y2006m03 |       1 | 03-17-2006 |       25 |        10
+ measurement_y2007m01 |       1 | 01-15-2007 |       10 |        10
+ measurement_y2007m01 |       1 | 01-16-2007 |       10 |        10
+ measurement_y2006m02 |       2 | 02-10-2006 |       20 |        20
+(7 rows)
+
+DROP TABLE measurement, new_measurement CASCADE;
+NOTICE:  drop cascades to 3 other objects
+DETAIL:  drop cascades to table measurement_y2006m02
+drop cascades to table measurement_y2006m03
+drop cascades to table measurement_y2007m01
+DROP FUNCTION measurement_insert_trigger();
+-- prepare
+RESET SESSION AUTHORIZATION;
+DROP TABLE target, target2;
+DROP TABLE source, source2;
+DROP FUNCTION merge_trigfunc();
+DROP USER merge_privs;
+DROP USER merge_no_privs;
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index 291e21d7a63..8b4b039c6a6 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -699,6 +699,104 @@ SELECT atest6 FROM atest6; -- ok
 (0 rows)
 
 COPY atest6 TO stdout; -- ok
+-- test column privileges with MERGE
+SET SESSION AUTHORIZATION regress_priv_user1;
+CREATE TABLE mtarget (a int, b text);
+CREATE TABLE msource (a int, b text);
+INSERT INTO mtarget VALUES (1, 'init1'), (2, 'init2');
+INSERT INTO msource VALUES (1, 'source1'), (2, 'source2'), (3, 'source3');
+GRANT SELECT (a) ON msource TO regress_priv_user4;
+GRANT SELECT (a) ON mtarget TO regress_priv_user4;
+GRANT INSERT (a,b) ON mtarget TO regress_priv_user4;
+GRANT UPDATE (b) ON mtarget TO regress_priv_user4;
+SET SESSION AUTHORIZATION regress_priv_user4;
+--
+-- test source privileges
+--
+-- fail (no SELECT priv on s.b)
+MERGE INTO mtarget t USING msource s ON t.a = s.a
+WHEN MATCHED THEN
+	UPDATE SET b = s.b
+WHEN NOT MATCHED THEN
+	INSERT VALUES (a, NULL);
+ERROR:  permission denied for table msource
+-- fail (s.b used in the INSERTed values)
+MERGE INTO mtarget t USING msource s ON t.a = s.a
+WHEN MATCHED THEN
+	UPDATE SET b = 'x'
+WHEN NOT MATCHED THEN
+	INSERT VALUES (a, b);
+ERROR:  permission denied for table msource
+-- fail (s.b used in the WHEN quals)
+MERGE INTO mtarget t USING msource s ON t.a = s.a
+WHEN MATCHED AND s.b = 'x' THEN
+	UPDATE SET b = 'x'
+WHEN NOT MATCHED THEN
+	INSERT VALUES (a, NULL);
+ERROR:  permission denied for table msource
+-- this should be ok since only s.a is accessed
+BEGIN;
+MERGE INTO mtarget t USING msource s ON t.a = s.a
+WHEN MATCHED THEN
+	UPDATE SET b = 'ok'
+WHEN NOT MATCHED THEN
+	INSERT VALUES (a, NULL);
+ROLLBACK;
+SET SESSION AUTHORIZATION regress_priv_user1;
+GRANT SELECT (b) ON msource TO regress_priv_user4;
+SET SESSION AUTHORIZATION regress_priv_user4;
+-- should now be ok
+BEGIN;
+MERGE INTO mtarget t USING msource s ON t.a = s.a
+WHEN MATCHED THEN
+	UPDATE SET b = s.b
+WHEN NOT MATCHED THEN
+	INSERT VALUES (a, b);
+ROLLBACK;
+--
+-- test target privileges
+--
+-- fail (no SELECT priv on t.b)
+MERGE INTO mtarget t USING msource s ON t.a = s.a
+WHEN MATCHED THEN
+	UPDATE SET b = t.b
+WHEN NOT MATCHED THEN
+	INSERT VALUES (a, NULL);
+ERROR:  permission denied for table mtarget
+-- fail (no UPDATE on t.a)
+MERGE INTO mtarget t USING msource s ON t.a = s.a
+WHEN MATCHED THEN
+	UPDATE SET b = s.b, a = t.a + 1
+WHEN NOT MATCHED THEN
+	INSERT VALUES (a, b);
+ERROR:  permission denied for table mtarget
+-- fail (no SELECT on t.b)
+MERGE INTO mtarget t USING msource s ON t.a = s.a
+WHEN MATCHED AND t.b IS NOT NULL THEN
+	UPDATE SET b = s.b
+WHEN NOT MATCHED THEN
+	INSERT VALUES (a, b);
+ERROR:  permission denied for table mtarget
+-- ok
+BEGIN;
+MERGE INTO mtarget t USING msource s ON t.a = s.a
+WHEN MATCHED THEN
+	UPDATE SET b = s.b;
+ROLLBACK;
+-- fail (no DELETE)
+MERGE INTO mtarget t USING msource s ON t.a = s.a
+WHEN MATCHED AND t.b IS NOT NULL THEN
+	DELETE;
+ERROR:  permission denied for table mtarget
+-- grant delete privileges
+SET SESSION AUTHORIZATION regress_priv_user1;
+GRANT DELETE ON mtarget TO regress_priv_user4;
+-- should be ok now
+BEGIN;
+MERGE INTO mtarget t USING msource s ON t.a = s.a
+WHEN MATCHED AND t.b IS NOT NULL THEN
+	DELETE;
+ROLLBACK;
 -- check error reporting with column privs
 SET SESSION AUTHORIZATION regress_priv_user1;
 CREATE TABLE t1 (c1 int, c2 int, c3 int check (c3 < 5), primary key (c1, c2));
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index d32a40ede33..b5f6eecba18 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -2118,6 +2118,188 @@ INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel')
     ON CONFLICT (did) DO UPDATE SET dauthor = 'regress_rls_carol';
 ERROR:  new row violates row-level security policy for table "document"
 --
+-- MERGE
+--
+RESET SESSION AUTHORIZATION;
+DROP POLICY p3_with_all ON document;
+ALTER TABLE document ADD COLUMN dnotes text DEFAULT '';
+-- all documents are readable
+CREATE POLICY p1 ON document FOR SELECT USING (true);
+-- one may insert documents only authored by them
+CREATE POLICY p2 ON document FOR INSERT WITH CHECK (dauthor = current_user);
+-- one may only update documents in 'novel' category
+CREATE POLICY p3 ON document FOR UPDATE
+  USING (cid = (SELECT cid from category WHERE cname = 'novel'))
+  WITH CHECK (dauthor = current_user);
+-- one may only delete documents in 'manga' category
+CREATE POLICY p4 ON document FOR DELETE
+  USING (cid = (SELECT cid from category WHERE cname = 'manga'));
+SELECT * FROM document;
+ did | cid | dlevel |      dauthor      |              dtitle              | dnotes 
+-----+-----+--------+-------------------+----------------------------------+--------
+   1 |  11 |      1 | regress_rls_bob   | my first novel                   | 
+   3 |  22 |      2 | regress_rls_bob   | my science fiction               | 
+   4 |  44 |      1 | regress_rls_bob   | my first manga                   | 
+   5 |  44 |      2 | regress_rls_bob   | my second manga                  | 
+   6 |  22 |      1 | regress_rls_carol | great science fiction            | 
+   7 |  33 |      2 | regress_rls_carol | great technology book            | 
+   8 |  44 |      1 | regress_rls_carol | great manga                      | 
+   9 |  22 |      1 | regress_rls_dave  | awesome science fiction          | 
+  10 |  33 |      2 | regress_rls_dave  | awesome technology book          | 
+  11 |  33 |      1 | regress_rls_carol | hoge                             | 
+  33 |  22 |      1 | regress_rls_bob   | okay science fiction             | 
+   2 |  11 |      2 | regress_rls_bob   | my first novel                   | 
+  78 |  33 |      1 | regress_rls_bob   | some technology novel            | 
+  79 |  33 |      1 | regress_rls_bob   | technology book, can only insert | 
+(14 rows)
+
+SET SESSION AUTHORIZATION regress_rls_bob;
+-- Fails, since update violates WITH CHECK qual on dauthor
+MERGE INTO document d
+USING (SELECT 1 as sdid) s
+ON did = s.sdid
+WHEN MATCHED THEN
+	UPDATE SET dnotes = dnotes || ' notes added by merge1 ', dauthor = 'regress_rls_alice';
+ERROR:  new row violates row-level security policy for table "document"
+-- Should be OK since USING and WITH CHECK quals pass
+MERGE INTO document d
+USING (SELECT 1 as sdid) s
+ON did = s.sdid
+WHEN MATCHED THEN
+	UPDATE SET dnotes = dnotes || ' notes added by merge2 ';
+-- Even when dauthor is updated explicitly, but to the existing value
+MERGE INTO document d
+USING (SELECT 1 as sdid) s
+ON did = s.sdid
+WHEN MATCHED THEN
+	UPDATE SET dnotes = dnotes || ' notes added by merge3 ', dauthor = 'regress_rls_bob';
+-- There is a MATCH for did = 3, but UPDATE's USING qual does not allow
+-- updating an item in category 'science fiction'
+MERGE INTO document d
+USING (SELECT 3 as sdid) s
+ON did = s.sdid
+WHEN MATCHED THEN
+	UPDATE SET dnotes = dnotes || ' notes added by merge ';
+ERROR:  target row violates row-level security policy (USING expression) for table "document"
+-- The same thing with DELETE action, but fails again because no permissions
+-- to delete items in 'science fiction' category that did 3 belongs to.
+MERGE INTO document d
+USING (SELECT 3 as sdid) s
+ON did = s.sdid
+WHEN MATCHED THEN
+	DELETE;
+ERROR:  target row violates row-level security policy (USING expression) for table "document"
+-- Document with did 4 belongs to 'manga' category which is allowed for
+-- deletion. But this fails because the UPDATE action is matched first and
+-- UPDATE policy does not allow updation in the category.
+MERGE INTO document d
+USING (SELECT 4 as sdid) s
+ON did = s.sdid
+WHEN MATCHED AND dnotes = '' THEN
+	UPDATE SET dnotes = dnotes || ' notes added by merge '
+WHEN MATCHED THEN
+	DELETE;
+ERROR:  target row violates row-level security policy (USING expression) for table "document"
+-- UPDATE action is not matched this time because of the WHEN qual.
+-- DELETE still fails because role regress_rls_bob does not have SELECT
+-- privileges on 'manga' category row in the category table.
+MERGE INTO document d
+USING (SELECT 4 as sdid) s
+ON did = s.sdid
+WHEN MATCHED AND dnotes <> '' THEN
+	UPDATE SET dnotes = dnotes || ' notes added by merge '
+WHEN MATCHED THEN
+	DELETE;
+ERROR:  target row violates row-level security policy (USING expression) for table "document"
+SELECT * FROM document WHERE did = 4;
+ did | cid | dlevel |     dauthor     |     dtitle     | dnotes 
+-----+-----+--------+-----------------+----------------+--------
+   4 |  44 |      1 | regress_rls_bob | my first manga | 
+(1 row)
+
+-- Switch to regress_rls_carol role and try the DELETE again. It should succeed
+-- this time
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION regress_rls_carol;
+MERGE INTO document d
+USING (SELECT 4 as sdid) s
+ON did = s.sdid
+WHEN MATCHED AND dnotes <> '' THEN
+	UPDATE SET dnotes = dnotes || ' notes added by merge '
+WHEN MATCHED THEN
+	DELETE;
+-- Switch back to regress_rls_bob role
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION regress_rls_bob;
+-- Try INSERT action. This fails because we are trying to insert
+-- dauthor = regress_rls_dave and INSERT's WITH CHECK does not allow
+-- that
+MERGE INTO document d
+USING (SELECT 12 as sdid) s
+ON did = s.sdid
+WHEN MATCHED THEN
+	DELETE
+WHEN NOT MATCHED THEN
+	INSERT VALUES (12, 11, 1, 'regress_rls_dave', 'another novel');
+ERROR:  new row violates row-level security policy for table "document"
+-- This should be fine
+MERGE INTO document d
+USING (SELECT 12 as sdid) s
+ON did = s.sdid
+WHEN MATCHED THEN
+	DELETE
+WHEN NOT MATCHED THEN
+	INSERT VALUES (12, 11, 1, 'regress_rls_bob', 'another novel');
+-- ok
+MERGE INTO document d
+USING (SELECT 1 as sdid) s
+ON did = s.sdid
+WHEN MATCHED THEN
+	UPDATE SET dnotes = dnotes || ' notes added by merge4 '
+WHEN NOT MATCHED THEN
+	INSERT VALUES (12, 11, 1, 'regress_rls_bob', 'another novel');
+-- drop and create a new SELECT policy which prevents us from reading
+-- any document except with category 'magna'
+RESET SESSION AUTHORIZATION;
+DROP POLICY p1 ON document;
+CREATE POLICY p1 ON document FOR SELECT
+  USING (cid = (SELECT cid from category WHERE cname = 'manga'));
+SET SESSION AUTHORIZATION regress_rls_bob;
+-- MERGE can no longer see the matching row and hence attempts the
+-- NOT MATCHED action, which results in unique key violation
+MERGE INTO document d
+USING (SELECT 1 as sdid) s
+ON did = s.sdid
+WHEN MATCHED THEN
+	UPDATE SET dnotes = dnotes || ' notes added by merge5 '
+WHEN NOT MATCHED THEN
+	INSERT VALUES (12, 11, 1, 'regress_rls_bob', 'another novel');
+ERROR:  duplicate key value violates unique constraint "document_pkey"
+RESET SESSION AUTHORIZATION;
+-- drop the restrictive SELECT policy so that we can look at the
+-- final state of the table
+DROP POLICY p1 ON document;
+-- Just check everything went per plan
+SELECT * FROM document;
+ did | cid | dlevel |      dauthor      |              dtitle              |                                dnotes                                 
+-----+-----+--------+-------------------+----------------------------------+-----------------------------------------------------------------------
+   3 |  22 |      2 | regress_rls_bob   | my science fiction               | 
+   5 |  44 |      2 | regress_rls_bob   | my second manga                  | 
+   6 |  22 |      1 | regress_rls_carol | great science fiction            | 
+   7 |  33 |      2 | regress_rls_carol | great technology book            | 
+   8 |  44 |      1 | regress_rls_carol | great manga                      | 
+   9 |  22 |      1 | regress_rls_dave  | awesome science fiction          | 
+  10 |  33 |      2 | regress_rls_dave  | awesome technology book          | 
+  11 |  33 |      1 | regress_rls_carol | hoge                             | 
+  33 |  22 |      1 | regress_rls_bob   | okay science fiction             | 
+   2 |  11 |      2 | regress_rls_bob   | my first novel                   | 
+  78 |  33 |      1 | regress_rls_bob   | some technology novel            | 
+  79 |  33 |      1 | regress_rls_bob   | technology book, can only insert | 
+  12 |  11 |      1 | regress_rls_bob   | another novel                    | 
+   1 |  11 |      1 | regress_rls_bob   | my first novel                   |  notes added by merge2  notes added by merge3  notes added by merge4 
+(14 rows)
+
+--
 -- ROLE/GROUP
 --
 SET SESSION AUTHORIZATION regress_rls_alice;
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 27d19b4bf1d..92e1a2f6d8c 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -3476,6 +3476,38 @@ CREATE RULE rules_parted_table_insert AS ON INSERT to rules_parted_table
 ALTER RULE rules_parted_table_insert ON rules_parted_table RENAME TO rules_parted_table_insert_redirect;
 DROP TABLE rules_parted_table;
 --
+-- test MERGE
+--
+CREATE TABLE rule_merge1 (a int, b text);
+CREATE TABLE rule_merge2 (a int, b text);
+CREATE RULE rule1 AS ON INSERT TO rule_merge1
+	DO INSTEAD INSERT INTO rule_merge2 VALUES (NEW.*);
+CREATE RULE rule2 AS ON UPDATE TO rule_merge1
+	DO INSTEAD UPDATE rule_merge2 SET a = NEW.a, b = NEW.b
+	WHERE a = OLD.a;
+CREATE RULE rule3 AS ON DELETE TO rule_merge1
+	DO INSTEAD DELETE FROM rule_merge2 WHERE a = OLD.a;
+-- MERGE not supported for table with rules
+MERGE INTO rule_merge1 t USING (SELECT 1 AS a) s
+	ON t.a = s.a
+	WHEN MATCHED AND t.a < 2 THEN
+		UPDATE SET b = b || ' updated by merge'
+	WHEN MATCHED AND t.a > 2 THEN
+		DELETE
+	WHEN NOT MATCHED THEN
+		INSERT VALUES (s.a, '');
+ERROR:  cannot execute MERGE on relation "rule_merge1"
+DETAIL:  MERGE is not supported for relations with rules.
+-- should be ok with the other table though
+MERGE INTO rule_merge2 t USING (SELECT 1 AS a) s
+	ON t.a = s.a
+	WHEN MATCHED AND t.a < 2 THEN
+		UPDATE SET b = b || ' updated by merge'
+	WHEN MATCHED AND t.a > 2 THEN
+		DELETE
+	WHEN NOT MATCHED THEN
+		INSERT VALUES (s.a, '');
+--
 -- Test enabling/disabling
 --
 CREATE TABLE ruletest1 (a int);
diff --git a/src/test/regress/expected/triggers.out b/src/test/regress/expected/triggers.out
index d3e02ca63b3..cd812336f2c 100644
--- a/src/test/regress/expected/triggers.out
+++ b/src/test/regress/expected/triggers.out
@@ -3284,6 +3284,54 @@ delete from self_ref where a = 1;
 NOTICE:  trigger_func(self_ref) called: action = DELETE, when = BEFORE, level = STATEMENT
 NOTICE:  trigger = self_ref_s_trig, old table = (1,), (2,1), (3,2), (4,3)
 drop table self_ref;
+--
+-- test transition tables with MERGE
+--
+create table merge_target_table (a int primary key, b text);
+create trigger merge_target_table_insert_trig
+  after insert on merge_target_table referencing new table as new_table
+  for each statement execute procedure dump_insert();
+create trigger merge_target_table_update_trig
+  after update on merge_target_table referencing old table as old_table new table as new_table
+  for each statement execute procedure dump_update();
+create trigger merge_target_table_delete_trig
+  after delete on merge_target_table referencing old table as old_table
+  for each statement execute procedure dump_delete();
+create table merge_source_table (a int, b text);
+insert into merge_source_table
+  values (1, 'initial1'), (2, 'initial2'),
+		 (3, 'initial3'), (4, 'initial4');
+merge into merge_target_table t
+using merge_source_table s
+on t.a = s.a
+when not matched then
+  insert values (a, b);
+NOTICE:  trigger = merge_target_table_insert_trig, new table = (1,initial1), (2,initial2), (3,initial3), (4,initial4)
+merge into merge_target_table t
+using merge_source_table s
+on t.a = s.a
+when matched and s.a <= 2 then
+	update set b = t.b || ' updated by merge'
+when matched and s.a > 2 then
+	delete
+when not matched then
+  insert values (a, b);
+NOTICE:  trigger = merge_target_table_delete_trig, old table = (3,initial3), (4,initial4)
+NOTICE:  trigger = merge_target_table_update_trig, old table = (1,initial1), (2,initial2), new table = (1,"initial1 updated by merge"), (2,"initial2 updated by merge")
+NOTICE:  trigger = merge_target_table_insert_trig, new table = <NULL>
+merge into merge_target_table t
+using merge_source_table s
+on t.a = s.a
+when matched and s.a <= 2 then
+	update set b = t.b || ' updated again by merge'
+when matched and s.a > 2 then
+	delete
+when not matched then
+  insert values (a, b);
+NOTICE:  trigger = merge_target_table_delete_trig, old table = <NULL>
+NOTICE:  trigger = merge_target_table_update_trig, old table = (1,"initial1 updated by merge"), (2,"initial2 updated by merge"), new table = (1,"initial1 updated by merge updated again by merge"), (2,"initial2 updated by merge updated again by merge")
+NOTICE:  trigger = merge_target_table_insert_trig, new table = (3,initial3), (4,initial4)
+drop table merge_source_table, merge_target_table;
 -- cleanup
 drop function dump_insert();
 drop function dump_update();
diff --git a/src/test/regress/expected/with.out b/src/test/regress/expected/with.out
index ff76ad4d6eb..7c6de7cc07c 100644
--- a/src/test/regress/expected/with.out
+++ b/src/test/regress/expected/with.out
@@ -2767,6 +2767,139 @@ RETURNING k, v;
 (0 rows)
 
 DROP TABLE withz;
+-- WITH referenced by MERGE statement
+CREATE TABLE m AS SELECT i AS k, (i || ' v')::text v FROM generate_series(1, 16, 3) i;
+ALTER TABLE m ADD UNIQUE (k);
+WITH RECURSIVE cte_basic AS (SELECT 1 a, 'cte_basic val' b)
+MERGE INTO m USING (select 0 k, 'merge source SubPlan' v) o ON m.k=o.k
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_basic WHERE cte_basic.a = m.k LIMIT 1)
+WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
+ERROR:  WITH RECURSIVE is not supported for MERGE statement
+-- Basic:
+WITH cte_basic AS MATERIALIZED (SELECT 1 a, 'cte_basic val' b)
+MERGE INTO m USING (select 0 k, 'merge source SubPlan' v offset 0) o ON m.k=o.k
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_basic WHERE cte_basic.a = m.k LIMIT 1)
+WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
+-- Examine
+SELECT * FROM m where k = 0;
+ k |          v           
+---+----------------------
+ 0 | merge source SubPlan
+(1 row)
+
+-- See EXPLAIN output for same query:
+EXPLAIN (VERBOSE, COSTS OFF)
+WITH cte_basic AS MATERIALIZED (SELECT 1 a, 'cte_basic val' b)
+MERGE INTO m USING (select 0 k, 'merge source SubPlan' v offset 0) o ON m.k=o.k
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_basic WHERE cte_basic.a = m.k LIMIT 1)
+WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
+                           QUERY PLAN                           
+----------------------------------------------------------------
+ Merge on public.m
+   CTE cte_basic
+     ->  Result
+           Output: 1, 'cte_basic val'::text
+   ->  Hash Right Join
+         Output: (0), ('merge source SubPlan'::text), m.ctid
+         Hash Cond: (m.k = (0))
+         ->  Seq Scan on public.m
+               Output: m.ctid, m.k
+         ->  Hash
+               Output: (0), ('merge source SubPlan'::text)
+               ->  Result
+                     Output: 0, 'merge source SubPlan'::text
+   SubPlan 2
+     ->  Limit
+           Output: ((cte_basic.b || ' merge update'::text))
+           ->  CTE Scan on cte_basic
+                 Output: (cte_basic.b || ' merge update'::text)
+                 Filter: (cte_basic.a = m.k)
+(19 rows)
+
+-- InitPlan
+WITH cte_init AS MATERIALIZED (SELECT 1 a, 'cte_init val' b)
+MERGE INTO m USING (select 1 k, 'merge source InitPlan' v offset 0) o ON m.k=o.k
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_init WHERE a = 1 LIMIT 1)
+WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
+-- Examine
+SELECT * FROM m where k = 1;
+ k |             v             
+---+---------------------------
+ 1 | cte_init val merge update
+(1 row)
+
+-- See EXPLAIN output for same query:
+EXPLAIN (VERBOSE, COSTS OFF)
+WITH cte_init AS MATERIALIZED (SELECT 1 a, 'cte_init val' b)
+MERGE INTO m USING (select 1 k, 'merge source InitPlan' v offset 0) o ON m.k=o.k
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_init WHERE a = 1 LIMIT 1)
+WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
+                          QUERY PLAN                           
+---------------------------------------------------------------
+ Merge on public.m
+   CTE cte_init
+     ->  Result
+           Output: 1, 'cte_init val'::text
+   InitPlan 2 (returns $1)
+     ->  Limit
+           Output: ((cte_init.b || ' merge update'::text))
+           ->  CTE Scan on cte_init
+                 Output: (cte_init.b || ' merge update'::text)
+                 Filter: (cte_init.a = 1)
+   ->  Hash Right Join
+         Output: (1), ('merge source InitPlan'::text), m.ctid
+         Hash Cond: (m.k = (1))
+         ->  Seq Scan on public.m
+               Output: m.ctid, m.k
+         ->  Hash
+               Output: (1), ('merge source InitPlan'::text)
+               ->  Result
+                     Output: 1, 'merge source InitPlan'::text
+(19 rows)
+
+-- MERGE source comes from CTE:
+WITH merge_source_cte AS MATERIALIZED (SELECT 15 a, 'merge_source_cte val' b)
+MERGE INTO m USING (select * from merge_source_cte) o ON m.k=o.a
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || merge_source_cte.*::text || ' merge update' FROM merge_source_cte WHERE a = 15)
+WHEN NOT MATCHED THEN INSERT VALUES(o.a, o.b || (SELECT merge_source_cte.*::text || ' merge insert' FROM merge_source_cte));
+-- Examine
+SELECT * FROM m where k = 15;
+ k  |                              v                               
+----+--------------------------------------------------------------
+ 15 | merge_source_cte val(15,"merge_source_cte val") merge insert
+(1 row)
+
+-- See EXPLAIN output for same query:
+EXPLAIN (VERBOSE, COSTS OFF)
+WITH merge_source_cte AS MATERIALIZED (SELECT 15 a, 'merge_source_cte val' b)
+MERGE INTO m USING (select * from merge_source_cte) o ON m.k=o.a
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || merge_source_cte.*::text || ' merge update' FROM merge_source_cte WHERE a = 15)
+WHEN NOT MATCHED THEN INSERT VALUES(o.a, o.b || (SELECT merge_source_cte.*::text || ' merge insert' FROM merge_source_cte));
+                                             QUERY PLAN                                              
+-----------------------------------------------------------------------------------------------------
+ Merge on public.m
+   CTE merge_source_cte
+     ->  Result
+           Output: 15, 'merge_source_cte val'::text
+   InitPlan 2 (returns $1)
+     ->  CTE Scan on merge_source_cte merge_source_cte_1
+           Output: ((merge_source_cte_1.b || (merge_source_cte_1.*)::text) || ' merge update'::text)
+           Filter: (merge_source_cte_1.a = 15)
+   InitPlan 3 (returns $2)
+     ->  CTE Scan on merge_source_cte merge_source_cte_2
+           Output: ((merge_source_cte_2.*)::text || ' merge insert'::text)
+   ->  Hash Right Join
+         Output: merge_source_cte.a, merge_source_cte.b, m.ctid
+         Hash Cond: (m.k = merge_source_cte.a)
+         ->  Seq Scan on public.m
+               Output: m.ctid, m.k
+         ->  Hash
+               Output: merge_source_cte.a, merge_source_cte.b
+               ->  CTE Scan on merge_source_cte
+                     Output: merge_source_cte.a, merge_source_cte.b
+(20 rows)
+
+DROP TABLE m;
 -- check that run to completion happens in proper ordering
 TRUNCATE TABLE y;
 INSERT INTO y SELECT generate_series(1, 3);
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 3ce701a588b..58fab1de1a0 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -86,7 +86,7 @@ test: brin_bloom brin_multi
 # psql depends on create_am
 # amutils depends on geometry, create_index_spgist, hash_index, brin
 # ----------
-test: create_table_like alter_generic alter_operator misc async dbsize misc_functions sysviews tsrf tid tidscan tidrangescan collate.icu.utf8 incremental_sort create_role
+test: create_table_like alter_generic alter_operator misc async dbsize merge misc_functions sysviews tsrf tid tidscan tidrangescan collate.icu.utf8 incremental_sort create_role
 
 # collate.*.utf8 tests cannot be run in parallel with each other
 test: rules psql psql_crosstab amutils stats_ext collate.linux.utf8
diff --git a/src/test/regress/sql/identity.sql b/src/test/regress/sql/identity.sql
index 52800f265c2..9b8db2e4a30 100644
--- a/src/test/regress/sql/identity.sql
+++ b/src/test/regress/sql/identity.sql
@@ -355,3 +355,49 @@ CREATE TABLE itest15 (id integer GENERATED ALWAYS AS IDENTITY NOT NULL);
 DROP TABLE itest15;
 CREATE TABLE itest15 (id integer NOT NULL GENERATED ALWAYS AS IDENTITY);
 DROP TABLE itest15;
+
+-- MERGE tests
+CREATE TABLE itest15 (a int GENERATED ALWAYS AS IDENTITY, b text);
+CREATE TABLE itest16 (a int GENERATED BY DEFAULT AS IDENTITY, b text);
+
+MERGE INTO itest15 t
+USING (SELECT 10 AS s_a, 'inserted by merge' AS s_b) s
+ON t.a = s.s_a
+WHEN NOT MATCHED THEN
+	INSERT (a, b) VALUES (s.s_a, s.s_b);
+
+-- Used to fail, but now it works and ignores the user supplied value
+MERGE INTO itest15 t
+USING (SELECT 20 AS s_a, 'inserted by merge' AS s_b) s
+ON t.a = s.s_a
+WHEN NOT MATCHED THEN
+	INSERT (a, b) OVERRIDING USER VALUE VALUES (s.s_a, s.s_b);
+
+MERGE INTO itest15 t
+USING (SELECT 30 AS s_a, 'inserted by merge' AS s_b) s
+ON t.a = s.s_a
+WHEN NOT MATCHED THEN
+	INSERT (a, b) OVERRIDING SYSTEM VALUE VALUES (s.s_a, s.s_b);
+
+MERGE INTO itest16 t
+USING (SELECT 10 AS s_a, 'inserted by merge' AS s_b) s
+ON t.a = s.s_a
+WHEN NOT MATCHED THEN
+	INSERT (a, b) VALUES (s.s_a, s.s_b);
+
+MERGE INTO itest16 t
+USING (SELECT 20 AS s_a, 'inserted by merge' AS s_b) s
+ON t.a = s.s_a
+WHEN NOT MATCHED THEN
+	INSERT (a, b) OVERRIDING USER VALUE VALUES (s.s_a, s.s_b);
+
+MERGE INTO itest16 t
+USING (SELECT 30 AS s_a, 'inserted by merge' AS s_b) s
+ON t.a = s.s_a
+WHEN NOT MATCHED THEN
+	INSERT (a, b) OVERRIDING SYSTEM VALUE VALUES (s.s_a, s.s_b);
+
+SELECT * FROM itest15;
+SELECT * FROM itest16;
+DROP TABLE itest15;
+DROP TABLE itest16;
diff --git a/src/test/regress/sql/merge.sql b/src/test/regress/sql/merge.sql
new file mode 100644
index 00000000000..34282e0794c
--- /dev/null
+++ b/src/test/regress/sql/merge.sql
@@ -0,0 +1,1273 @@
+--
+-- MERGE
+--
+--\set VERBOSITY verbose
+
+--set debug_print_rewritten = true;
+--set debug_print_parse = true;
+--set debug_print_pretty = true;
+
+
+CREATE USER merge_privs;
+CREATE USER merge_no_privs;
+DROP TABLE IF EXISTS target;
+DROP TABLE IF EXISTS source;
+CREATE TABLE target (tid integer, balance integer);
+CREATE TABLE source (sid integer, delta integer); --no index
+INSERT INTO target VALUES (1, 10);
+INSERT INTO target VALUES (2, 20);
+INSERT INTO target VALUES (3, 30);
+SELECT t.ctid is not null as matched, t.*, s.* FROM source s FULL OUTER JOIN target t ON s.sid = t.tid ORDER BY t.tid, s.sid;
+
+ALTER TABLE target OWNER TO merge_privs;
+ALTER TABLE source OWNER TO merge_privs;
+
+CREATE TABLE target2 (tid integer, balance integer);
+CREATE TABLE source2 (sid integer, delta integer);
+
+ALTER TABLE target2 OWNER TO merge_no_privs;
+ALTER TABLE source2 OWNER TO merge_no_privs;
+
+GRANT INSERT ON target TO merge_no_privs;
+
+SET SESSION AUTHORIZATION merge_privs;
+
+EXPLAIN (COSTS OFF)
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	DELETE;
+
+--
+-- Errors
+--
+MERGE INTO target t RANDOMWORD
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = 0;
+-- MATCHED/INSERT error
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	INSERT DEFAULT VALUES;
+-- incorrectly specifying INTO target
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT INTO target DEFAULT VALUES;
+-- Multiple VALUES clause
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT VALUES (1,1), (2,2);
+-- SELECT query for INSERT
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT SELECT (1, 1);
+-- NOT MATCHED/UPDATE
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	UPDATE SET balance = 0;
+-- UPDATE tablename
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	UPDATE target SET balance = 0;
+-- source and target names the same
+MERGE INTO target
+USING target
+ON tid = tid
+WHEN MATCHED THEN DO NOTHING;
+
+-- unsupported relation types
+-- view
+CREATE VIEW tv AS SELECT * FROM target;
+MERGE INTO tv t
+USING source s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT DEFAULT VALUES;
+DROP VIEW tv;
+
+-- materialized view
+CREATE MATERIALIZED VIEW mv AS SELECT * FROM target;
+MERGE INTO mv t
+USING source s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT DEFAULT VALUES;
+DROP MATERIALIZED VIEW mv;
+
+-- permissions
+
+MERGE INTO target
+USING source2
+ON target.tid = source2.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = 0;
+
+GRANT INSERT ON target TO merge_no_privs;
+SET SESSION AUTHORIZATION merge_no_privs;
+
+MERGE INTO target
+USING source2
+ON target.tid = source2.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = 0;
+
+GRANT UPDATE ON target2 TO merge_privs;
+SET SESSION AUTHORIZATION merge_privs;
+
+MERGE INTO target2
+USING source
+ON target2.tid = source.sid
+WHEN MATCHED THEN
+	DELETE;
+
+MERGE INTO target2
+USING source
+ON target2.tid = source.sid
+WHEN NOT MATCHED THEN
+	INSERT DEFAULT VALUES;
+
+-- check if the target can be accessed from source relation subquery; we should
+-- not be able to do so
+MERGE INTO target t
+USING (SELECT * FROM source WHERE t.tid > sid) s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT DEFAULT VALUES;
+
+--
+-- initial tests
+--
+-- zero rows in source has no effect
+MERGE INTO target
+USING source
+ON target.tid = source.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = 0;
+
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = 0;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	DELETE;
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT DEFAULT VALUES;
+ROLLBACK;
+
+-- insert some non-matching source rows to work from
+INSERT INTO source VALUES (4, 40);
+SELECT * FROM source ORDER BY sid;
+SELECT * FROM target ORDER BY tid;
+
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	DO NOTHING;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = 0;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	DELETE;
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT DEFAULT VALUES;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- index plans
+INSERT INTO target SELECT generate_series(1000,2500), 0;
+ALTER TABLE target ADD PRIMARY KEY (tid);
+ANALYZE target;
+
+EXPLAIN (COSTS OFF)
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = 0;
+EXPLAIN (COSTS OFF)
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	DELETE;
+EXPLAIN (COSTS OFF)
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT VALUES (4, NULL);
+DELETE FROM target WHERE tid > 100;
+ANALYZE target;
+
+-- insert some matching source rows to work from
+INSERT INTO source VALUES (2, 5);
+INSERT INTO source VALUES (3, 20);
+SELECT * FROM source ORDER BY sid;
+SELECT * FROM target ORDER BY tid;
+
+-- equivalent of an UPDATE join
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = 0;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- equivalent of a DELETE join
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	DELETE;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	DO NOTHING;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT VALUES (4, NULL);
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- duplicate source row causes multiple target row update ERROR
+INSERT INTO source VALUES (2, 5);
+SELECT * FROM source ORDER BY sid;
+SELECT * FROM target ORDER BY tid;
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = 0;
+ROLLBACK;
+
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	DELETE;
+ROLLBACK;
+
+-- remove duplicate MATCHED data from source data
+DELETE FROM source WHERE sid = 2;
+INSERT INTO source VALUES (2, 5);
+SELECT * FROM source ORDER BY sid;
+SELECT * FROM target ORDER BY tid;
+
+-- duplicate source row on INSERT should fail because of target_pkey
+INSERT INTO source VALUES (4, 40);
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+  INSERT VALUES (4, NULL);
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- remove duplicate NOT MATCHED data from source data
+DELETE FROM source WHERE sid = 4;
+INSERT INTO source VALUES (4, 40);
+SELECT * FROM source ORDER BY sid;
+SELECT * FROM target ORDER BY tid;
+
+-- remove constraints
+alter table target drop CONSTRAINT target_pkey;
+alter table target alter column tid drop not null;
+
+-- multiple actions
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT VALUES (4, 4)
+WHEN MATCHED THEN
+	UPDATE SET balance = 0;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- should be equivalent
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = 0
+WHEN NOT MATCHED THEN
+	INSERT VALUES (4, 4);
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- column references
+-- do a simple equivalent of an UPDATE join
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = t.balance + s.delta;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- do a simple equivalent of an INSERT SELECT
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT VALUES (s.sid, s.delta);
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- and again with duplicate source rows
+INSERT INTO source VALUES (5, 50);
+INSERT INTO source VALUES (5, 50);
+
+-- do a simple equivalent of an INSERT SELECT
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+  INSERT VALUES (s.sid, s.delta);
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- removing duplicate source rows
+DELETE FROM source WHERE sid = 5;
+
+-- and again with explicitly identified column list
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT (tid, balance) VALUES (s.sid, s.delta);
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- and again with a subtle error: referring to non-existent target row for NOT MATCHED
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT (tid, balance) VALUES (t.tid, s.delta);
+
+-- and again with a constant ON clause
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON (SELECT true)
+WHEN NOT MATCHED THEN
+	INSERT (tid, balance) VALUES (t.tid, s.delta);
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- now the classic UPSERT
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = t.balance + s.delta
+WHEN NOT MATCHED THEN
+	INSERT VALUES (s.sid, s.delta);
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- unreachable WHEN clause should ERROR
+BEGIN;
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN /* Terminal WHEN clause for MATCHED */
+	DELETE
+WHEN MATCHED AND s.delta > 0 THEN
+	UPDATE SET balance = t.balance - s.delta;
+ROLLBACK;
+
+-- conditional WHEN clause
+CREATE TABLE wq_target (tid integer not null, balance integer DEFAULT -1);
+CREATE TABLE wq_source (balance integer, sid integer);
+
+INSERT INTO wq_source (sid, balance) VALUES (1, 100);
+
+BEGIN;
+-- try a simple INSERT with default values first
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT (tid) VALUES (s.sid);
+SELECT * FROM wq_target;
+ROLLBACK;
+
+-- this time with a FALSE condition
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN NOT MATCHED AND FALSE THEN
+	INSERT (tid) VALUES (s.sid);
+SELECT * FROM wq_target;
+
+-- this time with an actual condition which returns false
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN NOT MATCHED AND s.balance <> 100 THEN
+	INSERT (tid) VALUES (s.sid);
+SELECT * FROM wq_target;
+
+BEGIN;
+-- and now with a condition which returns true
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN NOT MATCHED AND s.balance = 100 THEN
+	INSERT (tid) VALUES (s.sid);
+SELECT * FROM wq_target;
+ROLLBACK;
+
+-- conditions in the NOT MATCHED clause can only refer to source columns
+BEGIN;
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN NOT MATCHED AND t.balance = 100 THEN
+	INSERT (tid) VALUES (s.sid);
+SELECT * FROM wq_target;
+ROLLBACK;
+
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN NOT MATCHED AND s.balance = 100 THEN
+	INSERT (tid) VALUES (s.sid);
+SELECT * FROM wq_target;
+
+-- conditions in MATCHED clause can refer to both source and target
+SELECT * FROM wq_source;
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN MATCHED AND s.balance = 100 THEN
+	UPDATE SET balance = t.balance + s.balance;
+SELECT * FROM wq_target;
+
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN MATCHED AND t.balance = 100 THEN
+	UPDATE SET balance = t.balance + s.balance;
+SELECT * FROM wq_target;
+
+-- check if AND works
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN MATCHED AND t.balance = 99 AND s.balance > 100 THEN
+	UPDATE SET balance = t.balance + s.balance;
+SELECT * FROM wq_target;
+
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN MATCHED AND t.balance = 99 AND s.balance = 100 THEN
+	UPDATE SET balance = t.balance + s.balance;
+SELECT * FROM wq_target;
+
+-- check if OR works
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN MATCHED AND t.balance = 99 OR s.balance > 100 THEN
+	UPDATE SET balance = t.balance + s.balance;
+SELECT * FROM wq_target;
+
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN MATCHED AND t.balance = 199 OR s.balance > 100 THEN
+	UPDATE SET balance = t.balance + s.balance;
+SELECT * FROM wq_target;
+
+-- check if subqueries work in the conditions?
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN MATCHED AND t.balance > (SELECT max(balance) FROM target) THEN
+	UPDATE SET balance = t.balance + s.balance;
+
+-- check if we can access system columns in the conditions
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN MATCHED AND t.xmin = t.xmax THEN
+	UPDATE SET balance = t.balance + s.balance;
+
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN MATCHED AND t.tableoid >= 0 THEN
+	UPDATE SET balance = t.balance + s.balance;
+SELECT * FROM wq_target;
+
+-- test preventing WHEN conditions from writing to the database
+create or replace function merge_when_and_write() returns boolean
+language plpgsql as
+$$
+BEGIN
+	INSERT INTO target VALUES (100, 100);
+	RETURN TRUE;
+END;
+$$;
+
+BEGIN;
+MERGE INTO wq_target t
+USING wq_source s ON t.tid = s.sid
+WHEN MATCHED AND (merge_when_and_write()) THEN
+	UPDATE SET balance = t.balance + s.balance;
+ROLLBACK;
+drop function merge_when_and_write();
+
+DROP TABLE wq_target, wq_source;
+
+-- test triggers
+create or replace function merge_trigfunc () returns trigger
+language plpgsql as
+$$
+DECLARE
+	line text;
+BEGIN
+	SELECT INTO line format('%s %s %s trigger%s',
+		TG_WHEN, TG_OP, TG_LEVEL, CASE
+		WHEN TG_OP = 'INSERT' AND TG_LEVEL = 'ROW'
+			THEN format(' row: %s', NEW)
+		WHEN TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW'
+			THEN format(' row: %s -> %s', OLD, NEW)
+		WHEN TG_OP = 'DELETE' AND TG_LEVEL = 'ROW'
+			THEN format(' row: %s', OLD)
+		END);
+
+	RAISE NOTICE '%', line;
+	IF (TG_WHEN = 'BEFORE' AND TG_LEVEL = 'ROW') THEN
+		IF (TG_OP = 'DELETE') THEN
+			RETURN OLD;
+		ELSE
+			RETURN NEW;
+		END IF;
+	ELSE
+		RETURN NULL;
+	END IF;
+END;
+$$;
+CREATE TRIGGER merge_bsi BEFORE INSERT ON target FOR EACH STATEMENT EXECUTE PROCEDURE merge_trigfunc ();
+CREATE TRIGGER merge_bsu BEFORE UPDATE ON target FOR EACH STATEMENT EXECUTE PROCEDURE merge_trigfunc ();
+CREATE TRIGGER merge_bsd BEFORE DELETE ON target FOR EACH STATEMENT EXECUTE PROCEDURE merge_trigfunc ();
+CREATE TRIGGER merge_asi AFTER INSERT ON target FOR EACH STATEMENT EXECUTE PROCEDURE merge_trigfunc ();
+CREATE TRIGGER merge_asu AFTER UPDATE ON target FOR EACH STATEMENT EXECUTE PROCEDURE merge_trigfunc ();
+CREATE TRIGGER merge_asd AFTER DELETE ON target FOR EACH STATEMENT EXECUTE PROCEDURE merge_trigfunc ();
+CREATE TRIGGER merge_bri BEFORE INSERT ON target FOR EACH ROW EXECUTE PROCEDURE merge_trigfunc ();
+CREATE TRIGGER merge_bru BEFORE UPDATE ON target FOR EACH ROW EXECUTE PROCEDURE merge_trigfunc ();
+CREATE TRIGGER merge_brd BEFORE DELETE ON target FOR EACH ROW EXECUTE PROCEDURE merge_trigfunc ();
+CREATE TRIGGER merge_ari AFTER INSERT ON target FOR EACH ROW EXECUTE PROCEDURE merge_trigfunc ();
+CREATE TRIGGER merge_aru AFTER UPDATE ON target FOR EACH ROW EXECUTE PROCEDURE merge_trigfunc ();
+CREATE TRIGGER merge_ard AFTER DELETE ON target FOR EACH ROW EXECUTE PROCEDURE merge_trigfunc ();
+
+-- now the classic UPSERT, with a DELETE
+BEGIN;
+UPDATE target SET balance = 0 WHERE tid = 3;
+--EXPLAIN (ANALYZE ON, COSTS OFF, SUMMARY OFF, TIMING OFF)
+MERGE INTO target 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 THEN
+	INSERT VALUES (s.sid, s.delta);
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- Test behavior of triggers that turn UPDATE/DELETE into no-ops
+create or replace function skip_merge_op() returns trigger
+language plpgsql as
+$$
+BEGIN
+	RETURN NULL;
+END;
+$$;
+
+SELECT * FROM target full outer join source on (sid = tid);
+create trigger merge_skip BEFORE INSERT OR UPDATE or DELETE
+  ON target FOR EACH ROW EXECUTE FUNCTION skip_merge_op();
+MERGE INTO target t
+USING source AS s
+ON t.tid = s.sid
+WHEN MATCHED AND s.sid = 3 THEN UPDATE SET balance = t.balance + s.delta
+WHEN MATCHED THEN DELETE
+WHEN NOT MATCHED THEN INSERT VALUES (sid, delta);
+SELECT * FROM target FULL OUTER JOIN source ON (sid = tid);
+DROP TRIGGER merge_skip ON target;
+DROP FUNCTION skip_merge_op();
+
+-- test from PL/pgSQL
+-- make sure MERGE INTO isn't interpreted to mean returning variables like SELECT INTO
+BEGIN;
+DO LANGUAGE plpgsql $$
+BEGIN
+MERGE INTO target 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;
+END;
+$$;
+ROLLBACK;
+
+--source constants
+BEGIN;
+MERGE INTO target t
+USING (SELECT 9 AS sid, 57 AS delta) AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT (tid, balance) VALUES (s.sid, s.delta);
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+--source query
+BEGIN;
+MERGE INTO target t
+USING (SELECT sid, delta FROM source WHERE delta > 0) AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT (tid, balance) VALUES (s.sid, s.delta);
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+BEGIN;
+MERGE INTO target t
+USING (SELECT sid, delta as newname FROM source WHERE delta > 0) AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT (tid, balance) VALUES (s.sid, s.newname);
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+--self-merge
+BEGIN;
+MERGE INTO target t1
+USING target t2
+ON t1.tid = t2.tid
+WHEN MATCHED THEN
+	UPDATE SET balance = t1.balance + t2.balance
+WHEN NOT MATCHED THEN
+	INSERT VALUES (t2.tid, t2.balance);
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+BEGIN;
+MERGE INTO target t
+USING (SELECT tid as sid, balance as delta FROM target WHERE balance > 0) AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT (tid, balance) VALUES (s.sid, s.delta);
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+BEGIN;
+MERGE INTO target t
+USING
+(SELECT sid, max(delta) AS delta
+ FROM source
+ GROUP BY sid
+ HAVING count(*) = 1
+ ORDER BY sid ASC) AS s
+ON t.tid = s.sid
+WHEN NOT MATCHED THEN
+	INSERT (tid, balance) VALUES (s.sid, s.delta);
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- plpgsql parameters and results
+BEGIN;
+CREATE FUNCTION merge_func (p_id integer, p_bal integer)
+RETURNS INTEGER
+LANGUAGE plpgsql
+AS $$
+DECLARE
+ result integer;
+BEGIN
+MERGE INTO target t
+USING (SELECT p_id AS sid) AS s
+ON t.tid = s.sid
+WHEN MATCHED THEN
+	UPDATE SET balance = t.balance - p_bal;
+IF FOUND THEN
+	GET DIAGNOSTICS result := ROW_COUNT;
+END IF;
+RETURN result;
+END;
+$$;
+SELECT merge_func(3, 4);
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- PREPARE
+BEGIN;
+prepare foom as merge into target t using (select 1 as sid) s on (t.tid = s.sid) when matched then update set balance = 1;
+execute foom;
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+BEGIN;
+PREPARE foom2 (integer, integer) AS
+MERGE INTO target t
+USING (SELECT 1) s
+ON t.tid = $1
+WHEN MATCHED THEN
+UPDATE SET balance = $2;
+--EXPLAIN (ANALYZE ON, COSTS OFF, SUMMARY OFF, TIMING OFF)
+execute foom2 (1, 1);
+SELECT * FROM target ORDER BY tid;
+ROLLBACK;
+
+-- subqueries in source relation
+
+CREATE TABLE sq_target (tid integer NOT NULL, balance integer);
+CREATE TABLE sq_source (delta integer, sid integer, balance integer DEFAULT 0);
+
+INSERT INTO sq_target(tid, balance) VALUES (1,100), (2,200), (3,300);
+INSERT INTO sq_source(sid, delta) VALUES (1,10), (2,20), (4,40);
+
+BEGIN;
+MERGE INTO sq_target t
+USING (SELECT * FROM sq_source) s
+ON tid = sid
+WHEN MATCHED AND t.balance > delta THEN
+	UPDATE SET balance = t.balance + delta;
+SELECT * FROM sq_target;
+ROLLBACK;
+
+-- try a view
+CREATE VIEW v AS SELECT * FROM sq_source WHERE sid < 2;
+
+BEGIN;
+MERGE INTO sq_target
+USING v
+ON tid = sid
+WHEN MATCHED THEN
+    UPDATE SET balance = v.balance + delta;
+SELECT * FROM sq_target;
+ROLLBACK;
+
+-- ambiguous reference to a column
+BEGIN;
+MERGE INTO sq_target
+USING v
+ON tid = sid
+WHEN MATCHED AND tid > 2 THEN
+    UPDATE SET balance = balance + delta
+WHEN NOT MATCHED THEN
+	INSERT (balance, tid) VALUES (balance + delta, sid)
+WHEN MATCHED AND tid < 2 THEN
+	DELETE;
+ROLLBACK;
+
+BEGIN;
+INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10);
+MERGE INTO sq_target t
+USING v
+ON tid = sid
+WHEN MATCHED AND tid > 2 THEN
+    UPDATE SET balance = t.balance + delta
+WHEN NOT MATCHED THEN
+	INSERT (balance, tid) VALUES (balance + delta, sid)
+WHEN MATCHED AND tid < 2 THEN
+	DELETE;
+SELECT * FROM sq_target;
+ROLLBACK;
+
+-- CTEs
+BEGIN;
+INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10);
+WITH targq AS (
+	SELECT * FROM v
+)
+MERGE INTO sq_target t
+USING v
+ON tid = sid
+WHEN MATCHED AND tid > 2 THEN
+    UPDATE SET balance = t.balance + delta
+WHEN NOT MATCHED THEN
+	INSERT (balance, tid) VALUES (balance + delta, sid)
+WHEN MATCHED AND tid < 2 THEN
+	DELETE;
+ROLLBACK;
+
+-- RETURNING
+BEGIN;
+INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10);
+MERGE INTO sq_target t
+USING v
+ON tid = sid
+WHEN MATCHED AND tid > 2 THEN
+    UPDATE SET balance = t.balance + delta
+WHEN NOT MATCHED THEN
+	INSERT (balance, tid) VALUES (balance + delta, sid)
+WHEN MATCHED AND tid < 2 THEN
+	DELETE
+RETURNING *;
+ROLLBACK;
+
+-- EXPLAIN
+CREATE TABLE ex_mtarget (a int, b int);
+CREATE TABLE ex_msource (a int, b int);
+INSERT INTO ex_mtarget SELECT i, i*10 FROM generate_series(1,100,2) i;
+INSERT INTO ex_msource SELECT i, i*10 FROM generate_series(1,100,1) i;
+
+CREATE FUNCTION explain_merge(query text) RETURNS SETOF text
+LANGUAGE plpgsql AS
+$$
+DECLARE ln text;
+BEGIN
+    FOR ln IN
+        EXECUTE 'explain (analyze, timing off, summary off, costs off) ' ||
+		  query
+    LOOP
+        ln := regexp_replace(ln, 'Memory: \S*',  'Memory: xxx');
+        RETURN NEXT ln;
+    END LOOP;
+END;
+$$;
+
+-- only updates
+SELECT explain_merge('
+MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
+WHEN MATCHED THEN
+	UPDATE SET b = t.b + 1');
+
+-- only updates to selected tuples
+SELECT explain_merge('
+MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
+WHEN MATCHED AND t.a < 10 THEN
+	UPDATE SET b = t.b + 1');
+
+-- updates + deletes
+SELECT explain_merge('
+MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
+WHEN MATCHED AND t.a < 10 THEN
+	UPDATE SET b = t.b + 1
+WHEN MATCHED AND t.a >= 10 AND t.a <= 20 THEN
+	DELETE');
+
+-- only inserts
+SELECT explain_merge('
+MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
+WHEN NOT MATCHED AND s.a < 10 THEN
+	INSERT VALUES (a, b)');
+
+-- all three
+SELECT explain_merge('
+MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
+WHEN MATCHED AND t.a < 10 THEN
+	UPDATE SET b = t.b + 1
+WHEN MATCHED AND t.a >= 30 AND t.a <= 40 THEN
+	DELETE
+WHEN NOT MATCHED AND s.a < 20 THEN
+	INSERT VALUES (a, b)');
+
+DROP TABLE ex_msource, ex_mtarget;
+DROP FUNCTION explain_merge(text);
+
+-- Subqueries
+BEGIN;
+MERGE INTO sq_target t
+USING v
+ON tid = sid
+WHEN MATCHED THEN
+    UPDATE SET balance = (SELECT count(*) FROM sq_target);
+SELECT * FROM sq_target WHERE tid = 1;
+ROLLBACK;
+
+BEGIN;
+MERGE INTO sq_target t
+USING v
+ON tid = sid
+WHEN MATCHED AND (SELECT count(*) > 0 FROM sq_target) THEN
+    UPDATE SET balance = 42;
+SELECT * FROM sq_target WHERE tid = 1;
+ROLLBACK;
+
+BEGIN;
+MERGE INTO sq_target t
+USING v
+ON tid = sid AND (SELECT count(*) > 0 FROM sq_target)
+WHEN MATCHED THEN
+    UPDATE SET balance = 42;
+SELECT * FROM sq_target WHERE tid = 1;
+ROLLBACK;
+
+DROP TABLE sq_target, sq_source CASCADE;
+
+CREATE TABLE pa_target (tid integer, balance float, val text)
+	PARTITION BY LIST (tid);
+
+CREATE TABLE part1 PARTITION OF pa_target FOR VALUES IN (1,4);
+CREATE TABLE part2 PARTITION OF pa_target FOR VALUES IN (2,5,6);
+CREATE TABLE part3 PARTITION OF pa_target FOR VALUES IN (3,8,9);
+CREATE TABLE part4 PARTITION OF pa_target DEFAULT;
+
+CREATE TABLE pa_source (sid integer, delta float);
+-- insert many rows to the source table
+INSERT INTO pa_source SELECT id, id * 10  FROM generate_series(1,14) AS id;
+-- insert a few rows in the target table (odd numbered tid)
+INSERT INTO pa_target SELECT id, id * 100, 'initial' FROM generate_series(1,14,2) AS id;
+
+-- try simple MERGE
+BEGIN;
+MERGE INTO pa_target t
+  USING pa_source s
+  ON t.tid = s.sid
+  WHEN MATCHED THEN
+    UPDATE SET balance = balance + delta, val = val || ' updated by merge'
+  WHEN NOT MATCHED THEN
+    INSERT VALUES (sid, delta, 'inserted by merge');
+SELECT * FROM pa_target ORDER BY tid;
+ROLLBACK;
+
+-- same with a constant qual
+BEGIN;
+MERGE INTO pa_target t
+  USING pa_source s
+  ON t.tid = s.sid AND tid = 1
+  WHEN MATCHED THEN
+    UPDATE SET balance = balance + delta, val = val || ' updated by merge'
+  WHEN NOT MATCHED THEN
+    INSERT VALUES (sid, delta, 'inserted by merge');
+SELECT * FROM pa_target ORDER BY tid;
+ROLLBACK;
+
+-- try updating the partition key column
+BEGIN;
+MERGE INTO pa_target t
+  USING pa_source s
+  ON t.tid = s.sid
+  WHEN MATCHED THEN
+    UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge'
+  WHEN NOT MATCHED THEN
+    INSERT VALUES (sid, delta, 'inserted by merge');
+SELECT * FROM pa_target ORDER BY tid;
+ROLLBACK;
+
+DROP TABLE pa_target CASCADE;
+
+-- The target table is partitioned in the same way, but this time by attaching
+-- partitions which have columns in different order, dropped columns etc.
+CREATE TABLE pa_target (tid integer, balance float, val text)
+	PARTITION BY LIST (tid);
+
+CREATE TABLE part1 (tid integer, balance float, val text);
+CREATE TABLE part2 (balance float, tid integer, val text);
+CREATE TABLE part3 (tid integer, balance float, val text);
+CREATE TABLE part4 (extraid text, tid integer, balance float, val text);
+ALTER TABLE part4 DROP COLUMN extraid;
+
+ALTER TABLE pa_target ATTACH PARTITION part1 FOR VALUES IN (1,4);
+ALTER TABLE pa_target ATTACH PARTITION part2 FOR VALUES IN (2,5,6);
+ALTER TABLE pa_target ATTACH PARTITION part3 FOR VALUES IN (3,8,9);
+ALTER TABLE pa_target ATTACH PARTITION part4 DEFAULT;
+
+-- insert a few rows in the target table (odd numbered tid)
+INSERT INTO pa_target SELECT id, id * 100, 'initial' FROM generate_series(1,14,2) AS id;
+
+-- try simple MERGE
+BEGIN;
+MERGE INTO pa_target t
+  USING pa_source s
+  ON t.tid = s.sid
+  WHEN MATCHED THEN
+    UPDATE SET balance = balance + delta, val = val || ' updated by merge'
+  WHEN NOT MATCHED THEN
+    INSERT VALUES (sid, delta, 'inserted by merge');
+SELECT * FROM pa_target ORDER BY tid;
+ROLLBACK;
+
+-- same with a constant qual
+BEGIN;
+MERGE INTO pa_target t
+  USING pa_source s
+  ON t.tid = s.sid AND tid IN (1, 5)
+  WHEN MATCHED AND tid % 5 = 0 THEN DELETE
+  WHEN MATCHED THEN
+    UPDATE SET balance = balance + delta, val = val || ' updated by merge'
+  WHEN NOT MATCHED THEN
+    INSERT VALUES (sid, delta, 'inserted by merge');
+SELECT * FROM pa_target ORDER BY tid;
+ROLLBACK;
+
+-- try updating the partition key column
+BEGIN;
+MERGE INTO pa_target t
+  USING pa_source s
+  ON t.tid = s.sid
+  WHEN MATCHED THEN
+    UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge'
+  WHEN NOT MATCHED THEN
+    INSERT VALUES (sid, delta, 'inserted by merge');
+SELECT * FROM pa_target ORDER BY tid;
+ROLLBACK;
+
+DROP TABLE pa_source;
+DROP TABLE pa_target CASCADE;
+
+-- Sub-partitioning
+CREATE TABLE pa_target (logts timestamp, tid integer, balance float, val text)
+	PARTITION BY RANGE (logts);
+
+CREATE TABLE part_m01 PARTITION OF pa_target
+	FOR VALUES FROM ('2017-01-01') TO ('2017-02-01')
+	PARTITION BY LIST (tid);
+CREATE TABLE part_m01_odd PARTITION OF part_m01
+	FOR VALUES IN (1,3,5,7,9);
+CREATE TABLE part_m01_even PARTITION OF part_m01
+	FOR VALUES IN (2,4,6,8);
+CREATE TABLE part_m02 PARTITION OF pa_target
+	FOR VALUES FROM ('2017-02-01') TO ('2017-03-01')
+	PARTITION BY LIST (tid);
+CREATE TABLE part_m02_odd PARTITION OF part_m02
+	FOR VALUES IN (1,3,5,7,9);
+CREATE TABLE part_m02_even PARTITION OF part_m02
+	FOR VALUES IN (2,4,6,8);
+
+CREATE TABLE pa_source (sid integer, delta float);
+-- insert many rows to the source table
+INSERT INTO pa_source SELECT id, id * 10  FROM generate_series(1,14) AS id;
+-- insert a few rows in the target table (odd numbered tid)
+INSERT INTO pa_target SELECT '2017-01-31', id, id * 100, 'initial' FROM generate_series(1,9,3) AS id;
+INSERT INTO pa_target SELECT '2017-02-28', id, id * 100, 'initial' FROM generate_series(2,9,3) AS id;
+
+-- try simple MERGE
+BEGIN;
+MERGE INTO pa_target t
+  USING (SELECT '2017-01-15' AS slogts, * FROM pa_source WHERE sid < 10) s
+  ON t.tid = s.sid
+  WHEN MATCHED THEN
+    UPDATE SET balance = balance + delta, val = val || ' updated by merge'
+  WHEN NOT MATCHED THEN
+    INSERT VALUES (slogts::timestamp, sid, delta, 'inserted by merge');
+SELECT * FROM pa_target ORDER BY tid;
+ROLLBACK;
+
+DROP TABLE pa_source;
+DROP TABLE pa_target CASCADE;
+
+-- some complex joins on the source side
+
+CREATE TABLE cj_target (tid integer, balance float, val text);
+CREATE TABLE cj_source1 (sid1 integer, scat integer, delta integer);
+CREATE TABLE cj_source2 (sid2 integer, sval text);
+INSERT INTO cj_source1 VALUES (1, 10, 100);
+INSERT INTO cj_source1 VALUES (1, 20, 200);
+INSERT INTO cj_source1 VALUES (2, 20, 300);
+INSERT INTO cj_source1 VALUES (3, 10, 400);
+INSERT INTO cj_source2 VALUES (1, 'initial source2');
+INSERT INTO cj_source2 VALUES (2, 'initial source2');
+INSERT INTO cj_source2 VALUES (3, 'initial source2');
+
+-- source relation is an unaliased join
+MERGE INTO cj_target t
+USING cj_source1 s1
+	INNER JOIN cj_source2 s2 ON sid1 = sid2
+ON t.tid = sid1
+WHEN NOT MATCHED THEN
+	INSERT VALUES (sid1, delta, sval);
+
+-- try accessing columns from either side of the source join
+MERGE INTO cj_target t
+USING cj_source2 s2
+	INNER JOIN cj_source1 s1 ON sid1 = sid2 AND scat = 20
+ON t.tid = sid1
+WHEN NOT MATCHED THEN
+	INSERT VALUES (sid2, delta, sval)
+WHEN MATCHED THEN
+	DELETE;
+
+-- some simple expressions in INSERT targetlist
+MERGE INTO cj_target t
+USING cj_source2 s2
+	INNER JOIN cj_source1 s1 ON sid1 = sid2
+ON t.tid = sid1
+WHEN NOT MATCHED THEN
+	INSERT VALUES (sid2, delta + scat, sval)
+WHEN MATCHED THEN
+	UPDATE SET val = val || ' updated by merge';
+
+MERGE INTO cj_target t
+USING cj_source2 s2
+	INNER JOIN cj_source1 s1 ON sid1 = sid2 AND scat = 20
+ON t.tid = sid1
+WHEN MATCHED THEN
+	UPDATE SET val = val || ' ' || delta::text;
+
+SELECT * FROM cj_target;
+
+ALTER TABLE cj_source1 RENAME COLUMN sid1 TO sid;
+ALTER TABLE cj_source2 RENAME COLUMN sid2 TO sid;
+
+TRUNCATE cj_target;
+
+MERGE INTO cj_target t
+USING cj_source1 s1
+	INNER JOIN cj_source2 s2 ON s1.sid = s2.sid
+ON t.tid = s1.sid
+WHEN NOT MATCHED THEN
+	INSERT VALUES (s2.sid, delta, sval);
+
+DROP TABLE cj_source2, cj_source1, cj_target;
+
+-- Function scans
+CREATE TABLE fs_target (a int, b int, c text);
+MERGE INTO fs_target t
+USING generate_series(1,100,1) AS id
+ON t.a = id
+WHEN MATCHED THEN
+	UPDATE SET b = b + id
+WHEN NOT MATCHED THEN
+	INSERT VALUES (id, -1);
+
+MERGE INTO fs_target t
+USING generate_series(1,100,2) AS id
+ON t.a = id
+WHEN MATCHED THEN
+	UPDATE SET b = b + id, c = 'updated '|| id.*::text
+WHEN NOT MATCHED THEN
+	INSERT VALUES (id, -1, 'inserted ' || id.*::text);
+
+SELECT count(*) FROM fs_target;
+DROP TABLE fs_target;
+
+-- SERIALIZABLE test
+-- handled in isolation tests
+
+-- Inheritance-based partitioning
+CREATE TABLE measurement (
+    city_id         int not null,
+    logdate         date not null,
+    peaktemp        int,
+    unitsales       int
+);
+CREATE TABLE measurement_y2006m02 (
+    CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
+) INHERITS (measurement);
+CREATE TABLE measurement_y2006m03 (
+    CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
+) INHERITS (measurement);
+CREATE TABLE measurement_y2007m01 (
+    filler          text,
+    peaktemp        int,
+    logdate         date not null,
+    city_id         int not null,
+    unitsales       int
+    CHECK ( logdate >= DATE '2007-01-01' AND logdate < DATE '2007-02-01')
+);
+ALTER TABLE measurement_y2007m01 DROP COLUMN filler;
+ALTER TABLE measurement_y2007m01 INHERIT measurement;
+
+CREATE OR REPLACE FUNCTION measurement_insert_trigger()
+RETURNS TRIGGER AS $$
+BEGIN
+    IF ( NEW.logdate >= DATE '2006-02-01' AND
+         NEW.logdate < DATE '2006-03-01' ) THEN
+        INSERT INTO measurement_y2006m02 VALUES (NEW.*);
+    ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
+            NEW.logdate < DATE '2006-04-01' ) THEN
+        INSERT INTO measurement_y2006m03 VALUES (NEW.*);
+    ELSIF ( NEW.logdate >= DATE '2007-01-01' AND
+            NEW.logdate < DATE '2007-02-01' ) THEN
+        INSERT INTO measurement_y2007m01 (city_id, logdate, peaktemp, unitsales)
+            VALUES (NEW.*);
+    ELSE
+        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
+    END IF;
+    RETURN NULL;
+END;
+$$ LANGUAGE plpgsql ;
+CREATE TRIGGER insert_measurement_trigger
+    BEFORE INSERT ON measurement
+    FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();
+INSERT INTO measurement VALUES (1, '2006-02-10', 35, 10);
+INSERT INTO measurement VALUES (1, '2006-02-16', 45, 20);
+INSERT INTO measurement VALUES (1, '2006-03-17', 25, 10);
+INSERT INTO measurement VALUES (1, '2006-03-27', 15, 40);
+INSERT INTO measurement VALUES (1, '2007-01-15', 10, 10);
+INSERT INTO measurement VALUES (1, '2007-01-17', 10, 10);
+
+SELECT tableoid::regclass, * FROM measurement ORDER BY city_id, logdate;
+
+CREATE TABLE new_measurement (LIKE measurement);
+INSERT INTO new_measurement VALUES (1, '2006-03-01', 20, 10);
+INSERT INTO new_measurement VALUES (1, '2006-02-16', 50, 10);
+INSERT INTO new_measurement VALUES (2, '2006-02-10', 20, 20);
+INSERT INTO new_measurement VALUES (1, '2006-03-27', NULL, NULL);
+INSERT INTO new_measurement VALUES (1, '2007-01-17', NULL, NULL);
+INSERT INTO new_measurement VALUES (1, '2007-01-15', 5, NULL);
+INSERT INTO new_measurement VALUES (1, '2007-01-16', 10, 10);
+
+MERGE into measurement m
+ USING new_measurement nm ON
+      (m.city_id = nm.city_id and m.logdate=nm.logdate)
+WHEN MATCHED AND nm.peaktemp IS NULL THEN DELETE
+WHEN MATCHED THEN UPDATE
+     SET peaktemp = greatest(m.peaktemp, nm.peaktemp),
+        unitsales = m.unitsales + coalesce(nm.unitsales, 0)
+WHEN NOT MATCHED THEN INSERT
+     (city_id, logdate, peaktemp, unitsales)
+   VALUES (city_id, logdate, peaktemp, unitsales);
+
+SELECT tableoid::regclass, * FROM measurement ORDER BY city_id, logdate;
+DROP TABLE measurement, new_measurement CASCADE;
+DROP FUNCTION measurement_insert_trigger();
+
+-- prepare
+
+RESET SESSION AUTHORIZATION;
+DROP TABLE target, target2;
+DROP TABLE source, source2;
+DROP FUNCTION merge_trigfunc();
+DROP USER merge_privs;
+DROP USER merge_no_privs;
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index c8c545b64c7..32285728808 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -459,6 +459,114 @@ UPDATE atest5 SET one = 1; -- fail
 SELECT atest6 FROM atest6; -- ok
 COPY atest6 TO stdout; -- ok
 
+-- test column privileges with MERGE
+SET SESSION AUTHORIZATION regress_priv_user1;
+CREATE TABLE mtarget (a int, b text);
+CREATE TABLE msource (a int, b text);
+INSERT INTO mtarget VALUES (1, 'init1'), (2, 'init2');
+INSERT INTO msource VALUES (1, 'source1'), (2, 'source2'), (3, 'source3');
+
+GRANT SELECT (a) ON msource TO regress_priv_user4;
+GRANT SELECT (a) ON mtarget TO regress_priv_user4;
+GRANT INSERT (a,b) ON mtarget TO regress_priv_user4;
+GRANT UPDATE (b) ON mtarget TO regress_priv_user4;
+
+SET SESSION AUTHORIZATION regress_priv_user4;
+
+--
+-- test source privileges
+--
+
+-- fail (no SELECT priv on s.b)
+MERGE INTO mtarget t USING msource s ON t.a = s.a
+WHEN MATCHED THEN
+	UPDATE SET b = s.b
+WHEN NOT MATCHED THEN
+	INSERT VALUES (a, NULL);
+
+-- fail (s.b used in the INSERTed values)
+MERGE INTO mtarget t USING msource s ON t.a = s.a
+WHEN MATCHED THEN
+	UPDATE SET b = 'x'
+WHEN NOT MATCHED THEN
+	INSERT VALUES (a, b);
+
+-- fail (s.b used in the WHEN quals)
+MERGE INTO mtarget t USING msource s ON t.a = s.a
+WHEN MATCHED AND s.b = 'x' THEN
+	UPDATE SET b = 'x'
+WHEN NOT MATCHED THEN
+	INSERT VALUES (a, NULL);
+
+-- this should be ok since only s.a is accessed
+BEGIN;
+MERGE INTO mtarget t USING msource s ON t.a = s.a
+WHEN MATCHED THEN
+	UPDATE SET b = 'ok'
+WHEN NOT MATCHED THEN
+	INSERT VALUES (a, NULL);
+ROLLBACK;
+
+SET SESSION AUTHORIZATION regress_priv_user1;
+GRANT SELECT (b) ON msource TO regress_priv_user4;
+SET SESSION AUTHORIZATION regress_priv_user4;
+
+-- should now be ok
+BEGIN;
+MERGE INTO mtarget t USING msource s ON t.a = s.a
+WHEN MATCHED THEN
+	UPDATE SET b = s.b
+WHEN NOT MATCHED THEN
+	INSERT VALUES (a, b);
+ROLLBACK;
+
+--
+-- test target privileges
+--
+
+-- fail (no SELECT priv on t.b)
+MERGE INTO mtarget t USING msource s ON t.a = s.a
+WHEN MATCHED THEN
+	UPDATE SET b = t.b
+WHEN NOT MATCHED THEN
+	INSERT VALUES (a, NULL);
+
+-- fail (no UPDATE on t.a)
+MERGE INTO mtarget t USING msource s ON t.a = s.a
+WHEN MATCHED THEN
+	UPDATE SET b = s.b, a = t.a + 1
+WHEN NOT MATCHED THEN
+	INSERT VALUES (a, b);
+
+-- fail (no SELECT on t.b)
+MERGE INTO mtarget t USING msource s ON t.a = s.a
+WHEN MATCHED AND t.b IS NOT NULL THEN
+	UPDATE SET b = s.b
+WHEN NOT MATCHED THEN
+	INSERT VALUES (a, b);
+
+-- ok
+BEGIN;
+MERGE INTO mtarget t USING msource s ON t.a = s.a
+WHEN MATCHED THEN
+	UPDATE SET b = s.b;
+ROLLBACK;
+
+-- fail (no DELETE)
+MERGE INTO mtarget t USING msource s ON t.a = s.a
+WHEN MATCHED AND t.b IS NOT NULL THEN
+	DELETE;
+
+-- grant delete privileges
+SET SESSION AUTHORIZATION regress_priv_user1;
+GRANT DELETE ON mtarget TO regress_priv_user4;
+-- should be ok now
+BEGIN;
+MERGE INTO mtarget t USING msource s ON t.a = s.a
+WHEN MATCHED AND t.b IS NOT NULL THEN
+	DELETE;
+ROLLBACK;
+
 -- check error reporting with column privs
 SET SESSION AUTHORIZATION regress_priv_user1;
 CREATE TABLE t1 (c1 int, c2 int, c3 int check (c3 < 5), primary key (c1, c2));
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index b310acdd27c..febf3cc4cf1 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -811,6 +811,162 @@ INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel')
     ON CONFLICT (did) DO UPDATE SET dauthor = 'regress_rls_carol';
 
 --
+-- MERGE
+--
+RESET SESSION AUTHORIZATION;
+DROP POLICY p3_with_all ON document;
+
+ALTER TABLE document ADD COLUMN dnotes text DEFAULT '';
+-- all documents are readable
+CREATE POLICY p1 ON document FOR SELECT USING (true);
+-- one may insert documents only authored by them
+CREATE POLICY p2 ON document FOR INSERT WITH CHECK (dauthor = current_user);
+-- one may only update documents in 'novel' category
+CREATE POLICY p3 ON document FOR UPDATE
+  USING (cid = (SELECT cid from category WHERE cname = 'novel'))
+  WITH CHECK (dauthor = current_user);
+-- one may only delete documents in 'manga' category
+CREATE POLICY p4 ON document FOR DELETE
+  USING (cid = (SELECT cid from category WHERE cname = 'manga'));
+
+SELECT * FROM document;
+
+SET SESSION AUTHORIZATION regress_rls_bob;
+
+-- Fails, since update violates WITH CHECK qual on dauthor
+MERGE INTO document d
+USING (SELECT 1 as sdid) s
+ON did = s.sdid
+WHEN MATCHED THEN
+	UPDATE SET dnotes = dnotes || ' notes added by merge1 ', dauthor = 'regress_rls_alice';
+
+-- Should be OK since USING and WITH CHECK quals pass
+MERGE INTO document d
+USING (SELECT 1 as sdid) s
+ON did = s.sdid
+WHEN MATCHED THEN
+	UPDATE SET dnotes = dnotes || ' notes added by merge2 ';
+
+-- Even when dauthor is updated explicitly, but to the existing value
+MERGE INTO document d
+USING (SELECT 1 as sdid) s
+ON did = s.sdid
+WHEN MATCHED THEN
+	UPDATE SET dnotes = dnotes || ' notes added by merge3 ', dauthor = 'regress_rls_bob';
+
+-- There is a MATCH for did = 3, but UPDATE's USING qual does not allow
+-- updating an item in category 'science fiction'
+MERGE INTO document d
+USING (SELECT 3 as sdid) s
+ON did = s.sdid
+WHEN MATCHED THEN
+	UPDATE SET dnotes = dnotes || ' notes added by merge ';
+
+-- The same thing with DELETE action, but fails again because no permissions
+-- to delete items in 'science fiction' category that did 3 belongs to.
+MERGE INTO document d
+USING (SELECT 3 as sdid) s
+ON did = s.sdid
+WHEN MATCHED THEN
+	DELETE;
+
+-- Document with did 4 belongs to 'manga' category which is allowed for
+-- deletion. But this fails because the UPDATE action is matched first and
+-- UPDATE policy does not allow updation in the category.
+MERGE INTO document d
+USING (SELECT 4 as sdid) s
+ON did = s.sdid
+WHEN MATCHED AND dnotes = '' THEN
+	UPDATE SET dnotes = dnotes || ' notes added by merge '
+WHEN MATCHED THEN
+	DELETE;
+
+-- UPDATE action is not matched this time because of the WHEN qual.
+-- DELETE still fails because role regress_rls_bob does not have SELECT
+-- privileges on 'manga' category row in the category table.
+MERGE INTO document d
+USING (SELECT 4 as sdid) s
+ON did = s.sdid
+WHEN MATCHED AND dnotes <> '' THEN
+	UPDATE SET dnotes = dnotes || ' notes added by merge '
+WHEN MATCHED THEN
+	DELETE;
+
+SELECT * FROM document WHERE did = 4;
+
+-- Switch to regress_rls_carol role and try the DELETE again. It should succeed
+-- this time
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION regress_rls_carol;
+
+MERGE INTO document d
+USING (SELECT 4 as sdid) s
+ON did = s.sdid
+WHEN MATCHED AND dnotes <> '' THEN
+	UPDATE SET dnotes = dnotes || ' notes added by merge '
+WHEN MATCHED THEN
+	DELETE;
+
+-- Switch back to regress_rls_bob role
+RESET SESSION AUTHORIZATION;
+SET SESSION AUTHORIZATION regress_rls_bob;
+
+-- Try INSERT action. This fails because we are trying to insert
+-- dauthor = regress_rls_dave and INSERT's WITH CHECK does not allow
+-- that
+MERGE INTO document d
+USING (SELECT 12 as sdid) s
+ON did = s.sdid
+WHEN MATCHED THEN
+	DELETE
+WHEN NOT MATCHED THEN
+	INSERT VALUES (12, 11, 1, 'regress_rls_dave', 'another novel');
+
+-- This should be fine
+MERGE INTO document d
+USING (SELECT 12 as sdid) s
+ON did = s.sdid
+WHEN MATCHED THEN
+	DELETE
+WHEN NOT MATCHED THEN
+	INSERT VALUES (12, 11, 1, 'regress_rls_bob', 'another novel');
+
+-- ok
+MERGE INTO document d
+USING (SELECT 1 as sdid) s
+ON did = s.sdid
+WHEN MATCHED THEN
+	UPDATE SET dnotes = dnotes || ' notes added by merge4 '
+WHEN NOT MATCHED THEN
+	INSERT VALUES (12, 11, 1, 'regress_rls_bob', 'another novel');
+
+-- drop and create a new SELECT policy which prevents us from reading
+-- any document except with category 'magna'
+RESET SESSION AUTHORIZATION;
+DROP POLICY p1 ON document;
+CREATE POLICY p1 ON document FOR SELECT
+  USING (cid = (SELECT cid from category WHERE cname = 'manga'));
+
+SET SESSION AUTHORIZATION regress_rls_bob;
+
+-- MERGE can no longer see the matching row and hence attempts the
+-- NOT MATCHED action, which results in unique key violation
+MERGE INTO document d
+USING (SELECT 1 as sdid) s
+ON did = s.sdid
+WHEN MATCHED THEN
+	UPDATE SET dnotes = dnotes || ' notes added by merge5 '
+WHEN NOT MATCHED THEN
+	INSERT VALUES (12, 11, 1, 'regress_rls_bob', 'another novel');
+
+RESET SESSION AUTHORIZATION;
+-- drop the restrictive SELECT policy so that we can look at the
+-- final state of the table
+DROP POLICY p1 ON document;
+-- Just check everything went per plan
+SELECT * FROM document;
+
+--
 -- ROLE/GROUP
 --
 SET SESSION AUTHORIZATION regress_rls_alice;
diff --git a/src/test/regress/sql/rules.sql b/src/test/regress/sql/rules.sql
index aae2ba32e80..aee6abed860 100644
--- a/src/test/regress/sql/rules.sql
+++ b/src/test/regress/sql/rules.sql
@@ -1233,6 +1233,39 @@ ALTER RULE rules_parted_table_insert ON rules_parted_table RENAME TO rules_parte
 DROP TABLE rules_parted_table;
 
 --
+-- test MERGE
+--
+CREATE TABLE rule_merge1 (a int, b text);
+CREATE TABLE rule_merge2 (a int, b text);
+CREATE RULE rule1 AS ON INSERT TO rule_merge1
+	DO INSTEAD INSERT INTO rule_merge2 VALUES (NEW.*);
+CREATE RULE rule2 AS ON UPDATE TO rule_merge1
+	DO INSTEAD UPDATE rule_merge2 SET a = NEW.a, b = NEW.b
+	WHERE a = OLD.a;
+CREATE RULE rule3 AS ON DELETE TO rule_merge1
+	DO INSTEAD DELETE FROM rule_merge2 WHERE a = OLD.a;
+
+-- MERGE not supported for table with rules
+MERGE INTO rule_merge1 t USING (SELECT 1 AS a) s
+	ON t.a = s.a
+	WHEN MATCHED AND t.a < 2 THEN
+		UPDATE SET b = b || ' updated by merge'
+	WHEN MATCHED AND t.a > 2 THEN
+		DELETE
+	WHEN NOT MATCHED THEN
+		INSERT VALUES (s.a, '');
+
+-- should be ok with the other table though
+MERGE INTO rule_merge2 t USING (SELECT 1 AS a) s
+	ON t.a = s.a
+	WHEN MATCHED AND t.a < 2 THEN
+		UPDATE SET b = b || ' updated by merge'
+	WHEN MATCHED AND t.a > 2 THEN
+		DELETE
+	WHEN NOT MATCHED THEN
+		INSERT VALUES (s.a, '');
+
+--
 -- Test enabling/disabling
 --
 CREATE TABLE ruletest1 (a int);
diff --git a/src/test/regress/sql/triggers.sql b/src/test/regress/sql/triggers.sql
index 4cc096265db..83cd00f54f0 100644
--- a/src/test/regress/sql/triggers.sql
+++ b/src/test/regress/sql/triggers.sql
@@ -2439,6 +2439,53 @@ delete from self_ref where a = 1;
 
 drop table self_ref;
 
+--
+-- test transition tables with MERGE
+--
+create table merge_target_table (a int primary key, b text);
+create trigger merge_target_table_insert_trig
+  after insert on merge_target_table referencing new table as new_table
+  for each statement execute procedure dump_insert();
+create trigger merge_target_table_update_trig
+  after update on merge_target_table referencing old table as old_table new table as new_table
+  for each statement execute procedure dump_update();
+create trigger merge_target_table_delete_trig
+  after delete on merge_target_table referencing old table as old_table
+  for each statement execute procedure dump_delete();
+
+create table merge_source_table (a int, b text);
+insert into merge_source_table
+  values (1, 'initial1'), (2, 'initial2'),
+		 (3, 'initial3'), (4, 'initial4');
+
+merge into merge_target_table t
+using merge_source_table s
+on t.a = s.a
+when not matched then
+  insert values (a, b);
+
+merge into merge_target_table t
+using merge_source_table s
+on t.a = s.a
+when matched and s.a <= 2 then
+	update set b = t.b || ' updated by merge'
+when matched and s.a > 2 then
+	delete
+when not matched then
+  insert values (a, b);
+
+merge into merge_target_table t
+using merge_source_table s
+on t.a = s.a
+when matched and s.a <= 2 then
+	update set b = t.b || ' updated again by merge'
+when matched and s.a > 2 then
+	delete
+when not matched then
+  insert values (a, b);
+
+drop table merge_source_table, merge_target_table;
+
 -- cleanup
 drop function dump_insert();
 drop function dump_update();
diff --git a/src/test/regress/sql/with.sql b/src/test/regress/sql/with.sql
index e482177557c..f1ea3ae22e2 100644
--- a/src/test/regress/sql/with.sql
+++ b/src/test/regress/sql/with.sql
@@ -1270,6 +1270,62 @@ RETURNING k, v;
 
 DROP TABLE withz;
 
+-- WITH referenced by MERGE statement
+CREATE TABLE m AS SELECT i AS k, (i || ' v')::text v FROM generate_series(1, 16, 3) i;
+ALTER TABLE m ADD UNIQUE (k);
+
+WITH RECURSIVE cte_basic AS (SELECT 1 a, 'cte_basic val' b)
+MERGE INTO m USING (select 0 k, 'merge source SubPlan' v) o ON m.k=o.k
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_basic WHERE cte_basic.a = m.k LIMIT 1)
+WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
+
+-- Basic:
+WITH cte_basic AS MATERIALIZED (SELECT 1 a, 'cte_basic val' b)
+MERGE INTO m USING (select 0 k, 'merge source SubPlan' v offset 0) o ON m.k=o.k
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_basic WHERE cte_basic.a = m.k LIMIT 1)
+WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
+-- Examine
+SELECT * FROM m where k = 0;
+
+-- See EXPLAIN output for same query:
+EXPLAIN (VERBOSE, COSTS OFF)
+WITH cte_basic AS MATERIALIZED (SELECT 1 a, 'cte_basic val' b)
+MERGE INTO m USING (select 0 k, 'merge source SubPlan' v offset 0) o ON m.k=o.k
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_basic WHERE cte_basic.a = m.k LIMIT 1)
+WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
+
+-- InitPlan
+WITH cte_init AS MATERIALIZED (SELECT 1 a, 'cte_init val' b)
+MERGE INTO m USING (select 1 k, 'merge source InitPlan' v offset 0) o ON m.k=o.k
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_init WHERE a = 1 LIMIT 1)
+WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
+-- Examine
+SELECT * FROM m where k = 1;
+
+-- See EXPLAIN output for same query:
+EXPLAIN (VERBOSE, COSTS OFF)
+WITH cte_init AS MATERIALIZED (SELECT 1 a, 'cte_init val' b)
+MERGE INTO m USING (select 1 k, 'merge source InitPlan' v offset 0) o ON m.k=o.k
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || ' merge update' FROM cte_init WHERE a = 1 LIMIT 1)
+WHEN NOT MATCHED THEN INSERT VALUES(o.k, o.v);
+
+-- MERGE source comes from CTE:
+WITH merge_source_cte AS MATERIALIZED (SELECT 15 a, 'merge_source_cte val' b)
+MERGE INTO m USING (select * from merge_source_cte) o ON m.k=o.a
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || merge_source_cte.*::text || ' merge update' FROM merge_source_cte WHERE a = 15)
+WHEN NOT MATCHED THEN INSERT VALUES(o.a, o.b || (SELECT merge_source_cte.*::text || ' merge insert' FROM merge_source_cte));
+-- Examine
+SELECT * FROM m where k = 15;
+
+-- See EXPLAIN output for same query:
+EXPLAIN (VERBOSE, COSTS OFF)
+WITH merge_source_cte AS MATERIALIZED (SELECT 15 a, 'merge_source_cte val' b)
+MERGE INTO m USING (select * from merge_source_cte) o ON m.k=o.a
+WHEN MATCHED THEN UPDATE SET v = (SELECT b || merge_source_cte.*::text || ' merge update' FROM merge_source_cte WHERE a = 15)
+WHEN NOT MATCHED THEN INSERT VALUES(o.a, o.b || (SELECT merge_source_cte.*::text || ' merge insert' FROM merge_source_cte));
+
+DROP TABLE m;
+
 -- check that run to completion happens in proper ordering
 
 TRUNCATE TABLE y;