*** /home/amit/pg/mygit/src/test/regress/expected/merge.out 2018-01-30 11:50:31.297108552 +0900 --- /home/amit/pg/mygit/src/test/regress/results/merge.out 2018-03-23 13:18:25.034107527 +0900 *************** *** 39,52 **** WHEN MATCHED THEN DELETE ; ! QUERY PLAN ! ---------------------------------------- Merge on target t -> Merge Join ! Merge Cond: (t.tid = s.sid) -> Sort ! Sort Key: t.tid ! -> Seq Scan on target t -> Sort Sort Key: s.sid -> Seq Scan on source s --- 39,52 ---- 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 *************** *** 137,142 **** --- 137,154 ---- 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 -- *************** *** 229,242 **** WHEN MATCHED THEN UPDATE SET balance = 0 ; ! QUERY PLAN ! ---------------------------------------- Merge on target t -> Hash Join ! Hash Cond: (s.sid = t.tid) -> Seq Scan on source s -> Hash ! -> Seq Scan on target t (6 rows) EXPLAIN (COSTS OFF) --- 241,254 ---- 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) *************** *** 246,259 **** WHEN MATCHED THEN DELETE ; ! QUERY PLAN ! ---------------------------------------- Merge on target t -> Hash Join ! Hash Cond: (s.sid = t.tid) -> Seq Scan on source s -> Hash ! -> Seq Scan on target t (6 rows) EXPLAIN (COSTS OFF) --- 258,271 ---- 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) *************** *** 262,275 **** ON t.tid = s.sid WHEN NOT MATCHED THEN INSERT VALUES (4, NULL); ! QUERY PLAN ! ---------------------------------------- Merge on target t -> Hash Left Join ! Hash Cond: (s.sid = t.tid) -> Seq Scan on source s -> Hash ! -> Seq Scan on target t (6 rows) ; --- 274,287 ---- 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) ; *************** *** 370,375 **** --- 382,388 ---- UPDATE SET balance = 0 ; ERROR: MERGE command cannot affect row a second time + HINT: Ensure that not more than one source rows match any one target row ROLLBACK; BEGIN; MERGE INTO target t *************** *** 379,384 **** --- 392,398 ---- DELETE ; ERROR: MERGE command cannot affect row a second time + HINT: Ensure that not more than one source rows match any one target row ROLLBACK; -- correct source data DELETE FROM source WHERE sid = 2; *************** *** 696,701 **** --- 710,720 ---- 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 *************** *** 704,729 **** ERROR: system column "xmin" reference in WHEN AND condition is invalid LINE 3: WHEN MATCHED AND t.xmin = t.xmax THEN ^ 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; - ERROR: cannot use subquery in WHEN AND condition - LINE 3: WHEN MATCHED AND t.balance > (SELECT max(balance) FROM targe... - ^ SELECT * FROM wq_target; tid | balance -----+--------- ! 1 | 299 (1 row) DROP TABLE wq_target, wq_source; -- test triggers create or replace function merge_trigfunc () returns trigger --- 723,761 ---- 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 *************** *** 761,766 **** --- 793,799 ---- 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 *************** *** 783,788 **** --- 816,847 ---- 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 + -> 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 + (22 rows) + SELECT * FROM target ORDER BY tid; tid | balance -----+--------- *************** *** 879,884 **** --- 938,971 ---- 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 *************** *** 963,974 **** --- 1050,1108 ---- 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 + -> 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 + (11 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); *************** *** 1020,1027 **** ERROR: column reference "balance" is ambiguous LINE 5: UPDATE SET balance = balance + delta ^ - SELECT * FROM sq_target; - ERROR: current transaction is aborted, commands ignored until end of transaction block ROLLBACK; BEGIN; INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10); --- 1154,1159 ---- *************** *** 1043,1050 **** --- 1175,1593 ---- (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; + -- Subqueries + BEGIN; + MERGE INTO sq_target t + USING v + ON tid = sid + WHEN MATCHED THEN + UPDATE SET balance = (SELECT count(*) FROM sq_target) + ; + 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 + ; + 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 + ; + 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 ======================================================================