Revert "MERGE SQL Command following SQL:2016"

This reverts commit e6597dc3533946b98acba7871bd4ca1f7a3d4c1d.

10 files changed, 0 insertions, 3798 deletions

diff --git a/src/test/isolation/expected/merge-delete.out b/src/test/isolation/expected/merge-delete.out
deleted file mode 100644
index 40e62901b70..00000000000
--- a/src/test/isolation/expected/merge-delete.out
+++ /dev/null
@@ -1,97 +0,0 @@
-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            
-
-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            
-
-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            
-
-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            
-
-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        
-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        
-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            
-
-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            
-
-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        
-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        
-step c2: COMMIT;
diff --git a/src/test/isolation/expected/merge-insert-update.out b/src/test/isolation/expected/merge-insert-update.out
deleted file mode 100644
index 317fa16a3d5..00000000000
--- a/src/test/isolation/expected/merge-insert-update.out
+++ /dev/null
@@ -1,84 +0,0 @@
-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         
-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
-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 in steps c1 merge2: 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 in steps c1 merge2: 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         
-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
-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 in steps c1 merge2: 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        
-step c2: COMMIT;
diff --git a/src/test/isolation/expected/merge-match-recheck.out b/src/test/isolation/expected/merge-match-recheck.out
deleted file mode 100644
index 96a9f45ac88..00000000000
--- a/src/test/isolation/expected/merge-match-recheck.out
+++ /dev/null
@@ -1,106 +0,0 @@
-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
-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
-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
-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
-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
-step c1: COMMIT;
diff --git a/src/test/isolation/expected/merge-update.out b/src/test/isolation/expected/merge-update.out
deleted file mode 100644
index 60ae42ebd0f..00000000000
--- a/src/test/isolation/expected/merge-update.out
+++ /dev/null
@@ -1,213 +0,0 @@
-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
-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        
-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        
-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
-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        
-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        
-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_merge2a
-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_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_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            
-
-1              pa_merge2a     
-2              initial        
-2              initial updated by pa_merge1
-step c2: COMMIT;
diff --git a/src/test/isolation/specs/merge-delete.spec b/src/test/isolation/specs/merge-delete.spec
deleted file mode 100644
index 656954f8474..00000000000
--- a/src/test/isolation/specs/merge-delete.spec
+++ /dev/null
@@ -1,51 +0,0 @@
-# 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; }
-step "a1" { ABORT; }
-
-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
deleted file mode 100644
index 704492be1f3..00000000000
--- a/src/test/isolation/specs/merge-insert-update.spec
+++ /dev/null
@@ -1,52 +0,0 @@
-# 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; }
-step "a2" { ABORT; }
-
-# 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
deleted file mode 100644
index 193033da172..00000000000
--- a/src/test/isolation/specs/merge-match-recheck.spec
+++ /dev/null
@@ -1,79 +0,0 @@
-# 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; }
-step "a1" { ABORT; }
-
-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 "select2" { SELECT * FROM target; }
-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
deleted file mode 100644
index 64e849966ec..00000000000
--- a/src/test/isolation/specs/merge-update.spec
+++ /dev/null
@@ -1,132 +0,0 @@
-# MERGE UPDATE
-#
-# This test exercises atypical cases
-# 1. UPDATEs of PKs that change the join in the ON clause
-# 2. UPDATEs with WHEN AND 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_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 "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;
-}
-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"
diff --git a/src/test/regress/expected/merge.out b/src/test/regress/expected/merge.out
deleted file mode 100644
index a6fdb52f48a..00000000000
--- a/src/test/regress/expected/merge.out
+++ /dev/null
@@ -1,1811 +0,0 @@
---
--- 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_1.tid = s.sid)
-         ->  Sort
-               Sort Key: t_1.tid
-               ->  Seq Scan on target t_1
-         ->  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:  Multiple VALUES clauses not allowed in MERGE INSERT statement
-;
--- 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
-                ^
--- 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:  MERGE is not supported for this relation type
-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:  MERGE is not supported for this relation type
-DROP MATERIALIZED VIEW mv;
--- inherited table
-CREATE TABLE inhp (tid int, balance int);
-CREATE TABLE child1() INHERITS (inhp);
-CREATE TABLE child2() INHERITS (child1);
-MERGE INTO inhp t
-USING source s
-ON t.tid = s.sid
-WHEN NOT MATCHED THEN
-	INSERT DEFAULT VALUES;
-ERROR:  MERGE is not supported for relations with inheritance
-MERGE INTO child1 t
-USING source s
-ON t.tid = s.sid
-WHEN NOT MATCHED THEN
-	INSERT DEFAULT VALUES;
-ERROR:  MERGE is not supported for relations with inheritance
--- this should be ok
-MERGE INTO child2 t
-USING source s
-ON t.tid = s.sid
-WHEN NOT MATCHED THEN
-	INSERT DEFAULT VALUES;
-DROP TABLE inhp, child1, child2;
--- 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_1.tid)
-         ->  Seq Scan on source s
-         ->  Hash
-               ->  Seq Scan on target t_1
-(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_1.tid)
-         ->  Seq Scan on source s
-         ->  Hash
-               ->  Seq Scan on target t_1
-(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_1.tid)
-         ->  Seq Scan on source s
-         ->  Hash
-               ->  Seq Scan on target t_1
-(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 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;
--- correct 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)
-
--- 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 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:  system column "xmin" reference in WHEN AND condition is invalid
-LINE 3: WHEN MATCHED AND t.xmin = t.xmax THEN
-                         ^
-ALTER TABLE wq_target SET WITH OIDS;
-SELECT * FROM wq_target;
- tid | balance 
------+---------
-   1 |     399
-(1 row)
-
-MERGE INTO wq_target t
-USING wq_source s ON t.tid = s.sid
-WHEN MATCHED AND t.oid >= 0 THEN
-	UPDATE SET balance = t.balance + s.balance;
-SELECT * FROM wq_target;
- tid | balance 
------+---------
-   1 |     499
-(1 row)
-
--- test preventing WHEN AND 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
-$$
-BEGIN
-	RAISE NOTICE '% % % trigger', TG_WHEN, TG_OP, TG_LEVEL;
-	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
-NOTICE:  AFTER UPDATE ROW trigger
-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 INSERT ROW trigger
-NOTICE:  BEFORE DELETE ROW trigger
-NOTICE:  BEFORE UPDATE ROW trigger
-NOTICE:  AFTER INSERT ROW trigger
-NOTICE:  AFTER DELETE ROW trigger
-NOTICE:  AFTER UPDATE ROW trigger
-NOTICE:  AFTER DELETE STATEMENT trigger
-NOTICE:  AFTER UPDATE STATEMENT trigger
-NOTICE:  AFTER INSERT STATEMENT trigger
-                            QUERY PLAN                            
-------------------------------------------------------------------
- Merge on target t (actual rows=0 loops=1)
-   Tuples Inserted: 1
-   Tuples Updated: 1
-   Tuples Deleted: 1
-   Tuples Skipped: 0
-   ->  Hash Left Join (actual rows=3 loops=1)
-         Hash Cond: (s.sid = t_1.tid)
-         ->  Seq Scan on source s (actual rows=3 loops=1)
-         ->  Hash (actual rows=3 loops=1)
-               Buckets: 1024  Batches: 1  Memory Usage: 9kB
-               ->  Seq Scan on target t_1 (actual rows=3 loops=1)
- Trigger merge_ard: calls=1
- Trigger merge_ari: calls=1
- Trigger merge_aru: calls=1
- Trigger merge_asd: calls=1
- Trigger merge_asi: calls=1
- Trigger merge_asu: calls=1
- Trigger merge_brd: calls=1
- Trigger merge_bri: calls=1
- Trigger merge_bru: calls=1
- Trigger merge_bsd: calls=1
- Trigger merge_bsi: calls=1
- Trigger merge_bsu: calls=1
-(23 rows)
-
-SELECT * FROM target ORDER BY tid;
- tid | balance 
------+---------
-   1 |      10
-   2 |      15
-   4 |      40
-(3 rows)
-
-ROLLBACK;
--- 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
-NOTICE:  BEFORE UPDATE ROW trigger
-NOTICE:  AFTER UPDATE ROW trigger
-NOTICE:  AFTER UPDATE ROW trigger
-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
-NOTICE:  AFTER INSERT ROW trigger
-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
-NOTICE:  AFTER INSERT ROW trigger
-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
-NOTICE:  AFTER INSERT ROW trigger
-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
-NOTICE:  BEFORE UPDATE ROW trigger
-NOTICE:  BEFORE UPDATE ROW trigger
-NOTICE:  AFTER UPDATE ROW trigger
-NOTICE:  AFTER UPDATE ROW trigger
-NOTICE:  AFTER UPDATE ROW trigger
-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
-NOTICE:  AFTER INSERT ROW trigger
-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
-NOTICE:  AFTER UPDATE ROW trigger
-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
-NOTICE:  AFTER UPDATE ROW trigger
-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
-NOTICE:  AFTER UPDATE ROW trigger
-NOTICE:  AFTER UPDATE STATEMENT trigger
-                      QUERY PLAN                      
-------------------------------------------------------
- Merge on target t (actual rows=0 loops=1)
-   Tuples Inserted: 0
-   Tuples Updated: 1
-   Tuples Deleted: 0
-   Tuples Skipped: 0
-   ->  Seq Scan on target t_1 (actual rows=1 loops=1)
-         Filter: (tid = 1)
-         Rows Removed by Filter: 2
- Trigger merge_aru: calls=1
- Trigger merge_asu: calls=1
- Trigger merge_bru: calls=1
- Trigger merge_bsu: calls=1
-(12 rows)
-
-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
-;
-ERROR:  syntax error at or near "MERGE"
-LINE 4: MERGE INTO sq_target t
-        ^
-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;
--- only updates
-EXPLAIN (ANALYZE ON, COSTS OFF, SUMMARY OFF, TIMING OFF)
-MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
-WHEN MATCHED THEN
-	UPDATE SET b = t.b + 1;
-                              QUERY PLAN                               
------------------------------------------------------------------------
- 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_1.a = s.a)
-         ->  Sort (actual rows=50 loops=1)
-               Sort Key: t_1.a
-               Sort Method: quicksort  Memory: 27kB
-               ->  Seq Scan on ex_mtarget t_1 (actual rows=50 loops=1)
-         ->  Sort (actual rows=100 loops=1)
-               Sort Key: s.a
-               Sort Method: quicksort  Memory: 33kB
-               ->  Seq Scan on ex_msource s (actual rows=100 loops=1)
-(15 rows)
-
--- only updates to selected tuples
-EXPLAIN (ANALYZE ON, COSTS OFF, SUMMARY OFF, TIMING OFF)
-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;
-                              QUERY PLAN                               
------------------------------------------------------------------------
- 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_1.a = s.a)
-         ->  Sort (actual rows=50 loops=1)
-               Sort Key: t_1.a
-               Sort Method: quicksort  Memory: 27kB
-               ->  Seq Scan on ex_mtarget t_1 (actual rows=50 loops=1)
-         ->  Sort (actual rows=100 loops=1)
-               Sort Key: s.a
-               Sort Method: quicksort  Memory: 33kB
-               ->  Seq Scan on ex_msource s (actual rows=100 loops=1)
-(15 rows)
-
--- updates + deletes
-EXPLAIN (ANALYZE ON, COSTS OFF, SUMMARY OFF, TIMING OFF)
-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;
-                              QUERY PLAN                               
------------------------------------------------------------------------
- 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_1.a = s.a)
-         ->  Sort (actual rows=50 loops=1)
-               Sort Key: t_1.a
-               Sort Method: quicksort  Memory: 27kB
-               ->  Seq Scan on ex_mtarget t_1 (actual rows=50 loops=1)
-         ->  Sort (actual rows=100 loops=1)
-               Sort Key: s.a
-               Sort Method: quicksort  Memory: 33kB
-               ->  Seq Scan on ex_msource s (actual rows=100 loops=1)
-(15 rows)
-
--- only inserts
-EXPLAIN (ANALYZE ON, COSTS OFF, SUMMARY OFF, TIMING OFF)
-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);
-                              QUERY PLAN                               
------------------------------------------------------------------------
- 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_1.a)
-         ->  Sort (actual rows=100 loops=1)
-               Sort Key: s.a
-               Sort Method: quicksort  Memory: 33kB
-               ->  Seq Scan on ex_msource s (actual rows=100 loops=1)
-         ->  Sort (actual rows=45 loops=1)
-               Sort Key: t_1.a
-               Sort Method: quicksort  Memory: 27kB
-               ->  Seq Scan on ex_mtarget t_1 (actual rows=45 loops=1)
-(15 rows)
-
--- all three
-EXPLAIN (ANALYZE ON, COSTS OFF, SUMMARY OFF, TIMING OFF)
-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);
-                              QUERY PLAN                               
------------------------------------------------------------------------
- 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_1.a)
-         ->  Sort (actual rows=100 loops=1)
-               Sort Key: s.a
-               Sort Method: quicksort  Memory: 33kB
-               ->  Seq Scan on ex_msource s (actual rows=100 loops=1)
-         ->  Sort (actual rows=49 loops=1)
-               Sort Key: t_1.a
-               Sort Method: quicksort  Memory: 27kB
-               ->  Seq Scan on ex_mtarget t_1 (actual rows=49 loops=1)
-(15 rows)
-
-DROP TABLE ex_msource, ex_mtarget;
--- 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 = 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_source;
-DROP TABLE pa_target CASCADE;
--- Sub-partitionin
-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 unalised 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
--- 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/merge.sql b/src/test/regress/sql/merge.sql
deleted file mode 100644
index 585aa9455dc..00000000000
--- a/src/test/regress/sql/merge.sql
+++ /dev/null
@@ -1,1173 +0,0 @@
---
--- 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
-;
-
--- 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;
-
--- inherited table
-CREATE TABLE inhp (tid int, balance int);
-CREATE TABLE child1() INHERITS (inhp);
-CREATE TABLE child2() INHERITS (child1);
-
-MERGE INTO inhp t
-USING source s
-ON t.tid = s.sid
-WHEN NOT MATCHED THEN
-	INSERT DEFAULT VALUES;
-
-MERGE INTO child1 t
-USING source s
-ON t.tid = s.sid
-WHEN NOT MATCHED THEN
-	INSERT DEFAULT VALUES;
-
--- this should be ok
-MERGE INTO child2 t
-USING source s
-ON t.tid = s.sid
-WHEN NOT MATCHED THEN
-	INSERT DEFAULT VALUES;
-DROP TABLE inhp, child1, child2;
-
--- 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 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;
-
--- correct 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;
-
--- 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 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;
-
-ALTER TABLE wq_target SET WITH OIDS;
-SELECT * FROM wq_target;
-MERGE INTO wq_target t
-USING wq_source s ON t.tid = s.sid
-WHEN MATCHED AND t.oid >= 0 THEN
-	UPDATE SET balance = t.balance + s.balance;
-SELECT * FROM wq_target;
-
--- test preventing WHEN AND 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
-$$
-BEGIN
-	RAISE NOTICE '% % % trigger', TG_WHEN, TG_OP, TG_LEVEL;
-	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 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;
-
--- only updates
-EXPLAIN (ANALYZE ON, COSTS OFF, SUMMARY OFF, TIMING OFF)
-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
-EXPLAIN (ANALYZE ON, COSTS OFF, SUMMARY OFF, TIMING OFF)
-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
-EXPLAIN (ANALYZE ON, COSTS OFF, SUMMARY OFF, TIMING OFF)
-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
-EXPLAIN (ANALYZE ON, COSTS OFF, SUMMARY OFF, TIMING OFF)
-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
-EXPLAIN (ANALYZE ON, COSTS OFF, SUMMARY OFF, TIMING OFF)
-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;
-
--- 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 = 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_source;
-DROP TABLE pa_target CASCADE;
-
--- Sub-partitionin
-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 unalised 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
-
--- 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;