| blob | 28a69802d7297e96eadd24d58c1c668c7a5e35d4 |
1 --
2 -- MERGE
3 --
4 CREATE USER regress_merge_privs;
5 CREATE USER regress_merge_no_privs;
6 CREATE USER regress_merge_none;
7 DROP TABLE IF EXISTS target;
8 NOTICE: table "target" does not exist, skipping
9 DROP TABLE IF EXISTS source;
10 NOTICE: table "source" does not exist, skipping
11 CREATE TABLE target (tid integer, balance integer)
12 WITH (autovacuum_enabled=off);
13 CREATE TABLE source (sid integer, delta integer) -- no index
14 WITH (autovacuum_enabled=off);
15 INSERT INTO target VALUES (1, 10);
16 INSERT INTO target VALUES (2, 20);
17 INSERT INTO target VALUES (3, 30);
18 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;
19 matched | tid | balance | sid | delta
20 ---------+-----+---------+-----+-------
21 t | 1 | 10 | |
22 t | 2 | 20 | |
23 t | 3 | 30 | |
24 (3 rows)
26 ALTER TABLE target OWNER TO regress_merge_privs;
27 ALTER TABLE source OWNER TO regress_merge_privs;
28 CREATE TABLE target2 (tid integer, balance integer)
29 WITH (autovacuum_enabled=off);
30 CREATE TABLE source2 (sid integer, delta integer)
31 WITH (autovacuum_enabled=off);
32 ALTER TABLE target2 OWNER TO regress_merge_no_privs;
33 ALTER TABLE source2 OWNER TO regress_merge_no_privs;
34 GRANT INSERT ON target TO regress_merge_no_privs;
35 SET SESSION AUTHORIZATION regress_merge_privs;
36 EXPLAIN (COSTS OFF)
37 MERGE INTO target t
38 USING source AS s
39 ON t.tid = s.sid
40 WHEN MATCHED THEN
41 DELETE;
42 QUERY PLAN
43 ----------------------------------------
44 Merge on target t
45 -> Merge Join
46 Merge Cond: (t.tid = s.sid)
47 -> Sort
48 Sort Key: t.tid
49 -> Seq Scan on target t
50 -> Sort
51 Sort Key: s.sid
52 -> Seq Scan on source s
53 (9 rows)
55 --
56 -- Errors
57 --
58 MERGE INTO target t RANDOMWORD
59 USING source AS s
60 ON t.tid = s.sid
61 WHEN MATCHED THEN
62 UPDATE SET balance = 0;
63 ERROR: syntax error at or near "RANDOMWORD"
64 LINE 1: MERGE INTO target t RANDOMWORD
65 ^
66 -- MATCHED/INSERT error
67 MERGE INTO target t
68 USING source AS s
69 ON t.tid = s.sid
70 WHEN MATCHED THEN
71 INSERT DEFAULT VALUES;
72 ERROR: syntax error at or near "INSERT"
73 LINE 5: INSERT DEFAULT VALUES;
74 ^
75 -- incorrectly specifying INTO target
76 MERGE INTO target t
77 USING source AS s
78 ON t.tid = s.sid
79 WHEN NOT MATCHED THEN
80 INSERT INTO target DEFAULT VALUES;
81 ERROR: syntax error at or near "INTO"
82 LINE 5: INSERT INTO target DEFAULT VALUES;
83 ^
84 -- Multiple VALUES clause
85 MERGE INTO target t
86 USING source AS s
87 ON t.tid = s.sid
88 WHEN NOT MATCHED THEN
89 INSERT VALUES (1,1), (2,2);
90 ERROR: syntax error at or near ","
91 LINE 5: INSERT VALUES (1,1), (2,2);
92 ^
93 -- SELECT query for INSERT
94 MERGE INTO target t
95 USING source AS s
96 ON t.tid = s.sid
97 WHEN NOT MATCHED THEN
98 INSERT SELECT (1, 1);
99 ERROR: syntax error at or near "SELECT"
100 LINE 5: INSERT SELECT (1, 1);
101 ^
102 -- NOT MATCHED/UPDATE
103 MERGE INTO target t
104 USING source AS s
105 ON t.tid = s.sid
106 WHEN NOT MATCHED THEN
107 UPDATE SET balance = 0;
108 ERROR: syntax error at or near "UPDATE"
109 LINE 5: UPDATE SET balance = 0;
110 ^
111 -- UPDATE tablename
112 MERGE INTO target t
113 USING source AS s
114 ON t.tid = s.sid
115 WHEN MATCHED THEN
116 UPDATE target SET balance = 0;
117 ERROR: syntax error at or near "target"
118 LINE 5: UPDATE target SET balance = 0;
119 ^
120 -- source and target names the same
121 MERGE INTO target
122 USING target
123 ON tid = tid
124 WHEN MATCHED THEN DO NOTHING;
125 ERROR: name "target" specified more than once
126 DETAIL: The name is used both as MERGE target table and data source.
127 -- used in a CTE
128 WITH foo AS (
129 MERGE INTO target USING source ON (true)
130 WHEN MATCHED THEN DELETE
131 ) SELECT * FROM foo;
132 ERROR: MERGE not supported in WITH query
133 LINE 1: WITH foo AS (
134 ^
135 -- used in COPY
136 COPY (
137 MERGE INTO target USING source ON (true)
138 WHEN MATCHED THEN DELETE
139 ) TO stdout;
140 ERROR: MERGE not supported in COPY
141 -- unsupported relation types
142 -- view
143 CREATE VIEW tv AS SELECT * FROM target;
144 MERGE INTO tv t
145 USING source s
146 ON t.tid = s.sid
147 WHEN NOT MATCHED THEN
148 INSERT DEFAULT VALUES;
149 ERROR: cannot execute MERGE on relation "tv"
150 DETAIL: This operation is not supported for views.
151 DROP VIEW tv;
152 -- materialized view
153 CREATE MATERIALIZED VIEW mv AS SELECT * FROM target;
154 MERGE INTO mv t
155 USING source s
156 ON t.tid = s.sid
157 WHEN NOT MATCHED THEN
158 INSERT DEFAULT VALUES;
159 ERROR: cannot execute MERGE on relation "mv"
160 DETAIL: This operation is not supported for materialized views.
161 DROP MATERIALIZED VIEW mv;
162 -- permissions
163 SET SESSION AUTHORIZATION regress_merge_none;
164 MERGE INTO target
165 USING (SELECT 1)
166 ON true
167 WHEN MATCHED THEN
168 DO NOTHING;
169 ERROR: permission denied for table target
170 SET SESSION AUTHORIZATION regress_merge_privs;
171 MERGE INTO target
172 USING source2
173 ON target.tid = source2.sid
174 WHEN MATCHED THEN
175 UPDATE SET balance = 0;
176 ERROR: permission denied for table source2
177 GRANT INSERT ON target TO regress_merge_no_privs;
178 SET SESSION AUTHORIZATION regress_merge_no_privs;
179 MERGE INTO target
180 USING source2
181 ON target.tid = source2.sid
182 WHEN MATCHED THEN
183 UPDATE SET balance = 0;
184 ERROR: permission denied for table target
185 GRANT UPDATE ON target2 TO regress_merge_privs;
186 SET SESSION AUTHORIZATION regress_merge_privs;
187 MERGE INTO target2
188 USING source
189 ON target2.tid = source.sid
190 WHEN MATCHED THEN
191 DELETE;
192 ERROR: permission denied for table target2
193 MERGE INTO target2
194 USING source
195 ON target2.tid = source.sid
196 WHEN NOT MATCHED THEN
197 INSERT DEFAULT VALUES;
198 ERROR: permission denied for table target2
199 -- check if the target can be accessed from source relation subquery; we should
200 -- not be able to do so
201 MERGE INTO target t
202 USING (SELECT * FROM source WHERE t.tid > sid) s
203 ON t.tid = s.sid
204 WHEN NOT MATCHED THEN
205 INSERT DEFAULT VALUES;
206 ERROR: invalid reference to FROM-clause entry for table "t"
207 LINE 2: USING (SELECT * FROM source WHERE t.tid > sid) s
208 ^
209 DETAIL: There is an entry for table "t", but it cannot be referenced from this part of the query.
210 --
211 -- initial tests
212 --
213 -- zero rows in source has no effect
214 MERGE INTO target
215 USING source
216 ON target.tid = source.sid
217 WHEN MATCHED THEN
218 UPDATE SET balance = 0;
219 MERGE INTO target t
220 USING source AS s
221 ON t.tid = s.sid
222 WHEN MATCHED THEN
223 UPDATE SET balance = 0;
224 MERGE INTO target t
225 USING source AS s
226 ON t.tid = s.sid
227 WHEN MATCHED THEN
228 DELETE;
229 BEGIN;
230 MERGE INTO target t
231 USING source AS s
232 ON t.tid = s.sid
233 WHEN NOT MATCHED THEN
234 INSERT DEFAULT VALUES;
235 ROLLBACK;
236 -- insert some non-matching source rows to work from
237 INSERT INTO source VALUES (4, 40);
238 SELECT * FROM source ORDER BY sid;
239 sid | delta
240 -----+-------
241 4 | 40
242 (1 row)
244 SELECT * FROM target ORDER BY tid;
245 tid | balance
246 -----+---------
247 1 | 10
248 2 | 20
249 3 | 30
250 (3 rows)
252 MERGE INTO target t
253 USING source AS s
254 ON t.tid = s.sid
255 WHEN NOT MATCHED THEN
256 DO NOTHING;
257 MERGE INTO target t
258 USING source AS s
259 ON t.tid = s.sid
260 WHEN MATCHED THEN
261 UPDATE SET balance = 0;
262 MERGE INTO target t
263 USING source AS s
264 ON t.tid = s.sid
265 WHEN MATCHED THEN
266 DELETE;
267 BEGIN;
268 MERGE INTO target t
269 USING source AS s
270 ON t.tid = s.sid
271 WHEN NOT MATCHED THEN
272 INSERT DEFAULT VALUES;
273 SELECT * FROM target ORDER BY tid;
274 tid | balance
275 -----+---------
276 1 | 10
277 2 | 20
278 3 | 30
279 |
280 (4 rows)
282 ROLLBACK;
283 -- index plans
284 INSERT INTO target SELECT generate_series(1000,2500), 0;
285 ALTER TABLE target ADD PRIMARY KEY (tid);
286 ANALYZE target;
287 EXPLAIN (COSTS OFF)
288 MERGE INTO target t
289 USING source AS s
290 ON t.tid = s.sid
291 WHEN MATCHED THEN
292 UPDATE SET balance = 0;
293 QUERY PLAN
294 ----------------------------------------
295 Merge on target t
296 -> Hash Join
297 Hash Cond: (s.sid = t.tid)
298 -> Seq Scan on source s
299 -> Hash
300 -> Seq Scan on target t
301 (6 rows)
303 EXPLAIN (COSTS OFF)
304 MERGE INTO target t
305 USING source AS s
306 ON t.tid = s.sid
307 WHEN MATCHED THEN
308 DELETE;
309 QUERY PLAN
310 ----------------------------------------
311 Merge on target t
312 -> Hash Join
313 Hash Cond: (s.sid = t.tid)
314 -> Seq Scan on source s
315 -> Hash
316 -> Seq Scan on target t
317 (6 rows)
319 EXPLAIN (COSTS OFF)
320 MERGE INTO target t
321 USING source AS s
322 ON t.tid = s.sid
323 WHEN NOT MATCHED THEN
324 INSERT VALUES (4, NULL);
325 QUERY PLAN
326 ----------------------------------------
327 Merge on target t
328 -> Hash Left Join
329 Hash Cond: (s.sid = t.tid)
330 -> Seq Scan on source s
331 -> Hash
332 -> Seq Scan on target t
333 (6 rows)
335 DELETE FROM target WHERE tid > 100;
336 ANALYZE target;
337 -- insert some matching source rows to work from
338 INSERT INTO source VALUES (2, 5);
339 INSERT INTO source VALUES (3, 20);
340 SELECT * FROM source ORDER BY sid;
341 sid | delta
342 -----+-------
343 2 | 5
344 3 | 20
345 4 | 40
346 (3 rows)
348 SELECT * FROM target ORDER BY tid;
349 tid | balance
350 -----+---------
351 1 | 10
352 2 | 20
353 3 | 30
354 (3 rows)
356 -- equivalent of an UPDATE join
357 BEGIN;
358 MERGE INTO target t
359 USING source AS s
360 ON t.tid = s.sid
361 WHEN MATCHED THEN
362 UPDATE SET balance = 0;
363 SELECT * FROM target ORDER BY tid;
364 tid | balance
365 -----+---------
366 1 | 10
367 2 | 0
368 3 | 0
369 (3 rows)
371 ROLLBACK;
372 -- equivalent of a DELETE join
373 BEGIN;
374 MERGE INTO target t
375 USING source AS s
376 ON t.tid = s.sid
377 WHEN MATCHED THEN
378 DELETE;
379 SELECT * FROM target ORDER BY tid;
380 tid | balance
381 -----+---------
382 1 | 10
383 (1 row)
385 ROLLBACK;
386 BEGIN;
387 MERGE INTO target t
388 USING source AS s
389 ON t.tid = s.sid
390 WHEN MATCHED THEN
391 DO NOTHING;
392 SELECT * FROM target ORDER BY tid;
393 tid | balance
394 -----+---------
395 1 | 10
396 2 | 20
397 3 | 30
398 (3 rows)
400 ROLLBACK;
401 BEGIN;
402 MERGE INTO target t
403 USING source AS s
404 ON t.tid = s.sid
405 WHEN NOT MATCHED THEN
406 INSERT VALUES (4, NULL);
407 SELECT * FROM target ORDER BY tid;
408 tid | balance
409 -----+---------
410 1 | 10
411 2 | 20
412 3 | 30
413 4 |
414 (4 rows)
416 ROLLBACK;
417 -- duplicate source row causes multiple target row update ERROR
418 INSERT INTO source VALUES (2, 5);
419 SELECT * FROM source ORDER BY sid;
420 sid | delta
421 -----+-------
422 2 | 5
423 2 | 5
424 3 | 20
425 4 | 40
426 (4 rows)
428 SELECT * FROM target ORDER BY tid;
429 tid | balance
430 -----+---------
431 1 | 10
432 2 | 20
433 3 | 30
434 (3 rows)
436 BEGIN;
437 MERGE INTO target t
438 USING source AS s
439 ON t.tid = s.sid
440 WHEN MATCHED THEN
441 UPDATE SET balance = 0;
442 ERROR: MERGE command cannot affect row a second time
443 HINT: Ensure that not more than one source row matches any one target row.
444 ROLLBACK;
445 BEGIN;
446 MERGE INTO target t
447 USING source AS s
448 ON t.tid = s.sid
449 WHEN MATCHED THEN
450 DELETE;
451 ERROR: MERGE command cannot affect row a second time
452 HINT: Ensure that not more than one source row matches any one target row.
453 ROLLBACK;
454 -- remove duplicate MATCHED data from source data
455 DELETE FROM source WHERE sid = 2;
456 INSERT INTO source VALUES (2, 5);
457 SELECT * FROM source ORDER BY sid;
458 sid | delta
459 -----+-------
460 2 | 5
461 3 | 20
462 4 | 40
463 (3 rows)
465 SELECT * FROM target ORDER BY tid;
466 tid | balance
467 -----+---------
468 1 | 10
469 2 | 20
470 3 | 30
471 (3 rows)
473 -- duplicate source row on INSERT should fail because of target_pkey
474 INSERT INTO source VALUES (4, 40);
475 BEGIN;
476 MERGE INTO target t
477 USING source AS s
478 ON t.tid = s.sid
479 WHEN NOT MATCHED THEN
480 INSERT VALUES (4, NULL);
481 ERROR: duplicate key value violates unique constraint "target_pkey"
482 DETAIL: Key (tid)=(4) already exists.
483 SELECT * FROM target ORDER BY tid;
484 ERROR: current transaction is aborted, commands ignored until end of transaction block
485 ROLLBACK;
486 -- remove duplicate NOT MATCHED data from source data
487 DELETE FROM source WHERE sid = 4;
488 INSERT INTO source VALUES (4, 40);
489 SELECT * FROM source ORDER BY sid;
490 sid | delta
491 -----+-------
492 2 | 5
493 3 | 20
494 4 | 40
495 (3 rows)
497 SELECT * FROM target ORDER BY tid;
498 tid | balance
499 -----+---------
500 1 | 10
501 2 | 20
502 3 | 30
503 (3 rows)
505 -- remove constraints
506 alter table target drop CONSTRAINT target_pkey;
507 alter table target alter column tid drop not null;
508 -- multiple actions
509 BEGIN;
510 MERGE INTO target t
511 USING source AS s
512 ON t.tid = s.sid
513 WHEN NOT MATCHED THEN
514 INSERT VALUES (4, 4)
515 WHEN MATCHED THEN
516 UPDATE SET balance = 0;
517 SELECT * FROM target ORDER BY tid;
518 tid | balance
519 -----+---------
520 1 | 10
521 2 | 0
522 3 | 0
523 4 | 4
524 (4 rows)
526 ROLLBACK;
527 -- should be equivalent
528 BEGIN;
529 MERGE INTO target t
530 USING source AS s
531 ON t.tid = s.sid
532 WHEN MATCHED THEN
533 UPDATE SET balance = 0
534 WHEN NOT MATCHED THEN
535 INSERT VALUES (4, 4);
536 SELECT * FROM target ORDER BY tid;
537 tid | balance
538 -----+---------
539 1 | 10
540 2 | 0
541 3 | 0
542 4 | 4
543 (4 rows)
545 ROLLBACK;
546 -- column references
547 -- do a simple equivalent of an UPDATE join
548 BEGIN;
549 MERGE INTO target t
550 USING source AS s
551 ON t.tid = s.sid
552 WHEN MATCHED THEN
553 UPDATE SET balance = t.balance + s.delta;
554 SELECT * FROM target ORDER BY tid;
555 tid | balance
556 -----+---------
557 1 | 10
558 2 | 25
559 3 | 50
560 (3 rows)
562 ROLLBACK;
563 -- do a simple equivalent of an INSERT SELECT
564 BEGIN;
565 MERGE INTO target t
566 USING source AS s
567 ON t.tid = s.sid
568 WHEN NOT MATCHED THEN
569 INSERT VALUES (s.sid, s.delta);
570 SELECT * FROM target ORDER BY tid;
571 tid | balance
572 -----+---------
573 1 | 10
574 2 | 20
575 3 | 30
576 4 | 40
577 (4 rows)
579 ROLLBACK;
580 -- and again with duplicate source rows
581 INSERT INTO source VALUES (5, 50);
582 INSERT INTO source VALUES (5, 50);
583 -- do a simple equivalent of an INSERT SELECT
584 BEGIN;
585 MERGE INTO target t
586 USING source AS s
587 ON t.tid = s.sid
588 WHEN NOT MATCHED THEN
589 INSERT VALUES (s.sid, s.delta);
590 SELECT * FROM target ORDER BY tid;
591 tid | balance
592 -----+---------
593 1 | 10
594 2 | 20
595 3 | 30
596 4 | 40
597 5 | 50
598 5 | 50
599 (6 rows)
601 ROLLBACK;
602 -- removing duplicate source rows
603 DELETE FROM source WHERE sid = 5;
604 -- and again with explicitly identified column list
605 BEGIN;
606 MERGE INTO target t
607 USING source AS s
608 ON t.tid = s.sid
609 WHEN NOT MATCHED THEN
610 INSERT (tid, balance) VALUES (s.sid, s.delta);
611 SELECT * FROM target ORDER BY tid;
612 tid | balance
613 -----+---------
614 1 | 10
615 2 | 20
616 3 | 30
617 4 | 40
618 (4 rows)
620 ROLLBACK;
621 -- and again with a subtle error: referring to non-existent target row for NOT MATCHED
622 MERGE INTO target t
623 USING source AS s
624 ON t.tid = s.sid
625 WHEN NOT MATCHED THEN
626 INSERT (tid, balance) VALUES (t.tid, s.delta);
627 ERROR: invalid reference to FROM-clause entry for table "t"
628 LINE 5: INSERT (tid, balance) VALUES (t.tid, s.delta);
629 ^
630 DETAIL: There is an entry for table "t", but it cannot be referenced from this part of the query.
631 -- and again with a constant ON clause
632 BEGIN;
633 MERGE INTO target t
634 USING source AS s
635 ON (SELECT true)
636 WHEN NOT MATCHED THEN
637 INSERT (tid, balance) VALUES (t.tid, s.delta);
638 ERROR: invalid reference to FROM-clause entry for table "t"
639 LINE 5: INSERT (tid, balance) VALUES (t.tid, s.delta);
640 ^
641 DETAIL: There is an entry for table "t", but it cannot be referenced from this part of the query.
642 SELECT * FROM target ORDER BY tid;
643 ERROR: current transaction is aborted, commands ignored until end of transaction block
644 ROLLBACK;
645 -- now the classic UPSERT
646 BEGIN;
647 MERGE INTO target t
648 USING source AS s
649 ON t.tid = s.sid
650 WHEN MATCHED THEN
651 UPDATE SET balance = t.balance + s.delta
652 WHEN NOT MATCHED THEN
653 INSERT VALUES (s.sid, s.delta);
654 SELECT * FROM target ORDER BY tid;
655 tid | balance
656 -----+---------
657 1 | 10
658 2 | 25
659 3 | 50
660 4 | 40
661 (4 rows)
663 ROLLBACK;
664 -- unreachable WHEN clause should ERROR
665 BEGIN;
666 MERGE INTO target t
667 USING source AS s
668 ON t.tid = s.sid
669 WHEN MATCHED THEN /* Terminal WHEN clause for MATCHED */
670 DELETE
671 WHEN MATCHED THEN
672 UPDATE SET balance = t.balance - s.delta;
673 ERROR: unreachable WHEN clause specified after unconditional WHEN clause
674 ROLLBACK;
675 -- conditional WHEN clause
676 CREATE TABLE wq_target (tid integer not null, balance integer DEFAULT -1)
677 WITH (autovacuum_enabled=off);
678 CREATE TABLE wq_source (balance integer, sid integer)
679 WITH (autovacuum_enabled=off);
680 INSERT INTO wq_source (sid, balance) VALUES (1, 100);
681 BEGIN;
682 -- try a simple INSERT with default values first
683 MERGE INTO wq_target t
684 USING wq_source s ON t.tid = s.sid
685 WHEN NOT MATCHED THEN
686 INSERT (tid) VALUES (s.sid);
687 SELECT * FROM wq_target;
688 tid | balance
689 -----+---------
690 1 | -1
691 (1 row)
693 ROLLBACK;
694 -- this time with a FALSE condition
695 MERGE INTO wq_target t
696 USING wq_source s ON t.tid = s.sid
697 WHEN NOT MATCHED AND FALSE THEN
698 INSERT (tid) VALUES (s.sid);
699 SELECT * FROM wq_target;
700 tid | balance
701 -----+---------
702 (0 rows)
704 -- this time with an actual condition which returns false
705 MERGE INTO wq_target t
706 USING wq_source s ON t.tid = s.sid
707 WHEN NOT MATCHED AND s.balance <> 100 THEN
708 INSERT (tid) VALUES (s.sid);
709 SELECT * FROM wq_target;
710 tid | balance
711 -----+---------
712 (0 rows)
714 BEGIN;
715 -- and now with a condition which returns true
716 MERGE INTO wq_target t
717 USING wq_source s ON t.tid = s.sid
718 WHEN NOT MATCHED AND s.balance = 100 THEN
719 INSERT (tid) VALUES (s.sid);
720 SELECT * FROM wq_target;
721 tid | balance
722 -----+---------
723 1 | -1
724 (1 row)
726 ROLLBACK;
727 -- conditions in the NOT MATCHED clause can only refer to source columns
728 BEGIN;
729 MERGE INTO wq_target t
730 USING wq_source s ON t.tid = s.sid
731 WHEN NOT MATCHED AND t.balance = 100 THEN
732 INSERT (tid) VALUES (s.sid);
733 ERROR: invalid reference to FROM-clause entry for table "t"
734 LINE 3: WHEN NOT MATCHED AND t.balance = 100 THEN
735 ^
736 DETAIL: There is an entry for table "t", but it cannot be referenced from this part of the query.
737 SELECT * FROM wq_target;
738 ERROR: current transaction is aborted, commands ignored until end of transaction block
739 ROLLBACK;
740 MERGE INTO wq_target t
741 USING wq_source s ON t.tid = s.sid
742 WHEN NOT MATCHED AND s.balance = 100 THEN
743 INSERT (tid) VALUES (s.sid);
744 SELECT * FROM wq_target;
745 tid | balance
746 -----+---------
747 1 | -1
748 (1 row)
750 -- conditions in MATCHED clause can refer to both source and target
751 SELECT * FROM wq_source;
752 balance | sid
753 ---------+-----
754 100 | 1
755 (1 row)
757 MERGE INTO wq_target t
758 USING wq_source s ON t.tid = s.sid
759 WHEN MATCHED AND s.balance = 100 THEN
760 UPDATE SET balance = t.balance + s.balance;
761 SELECT * FROM wq_target;
762 tid | balance
763 -----+---------
764 1 | 99
765 (1 row)
767 MERGE INTO wq_target t
768 USING wq_source s ON t.tid = s.sid
769 WHEN MATCHED AND t.balance = 100 THEN
770 UPDATE SET balance = t.balance + s.balance;
771 SELECT * FROM wq_target;
772 tid | balance
773 -----+---------
774 1 | 99
775 (1 row)
777 -- check if AND works
778 MERGE INTO wq_target t
779 USING wq_source s ON t.tid = s.sid
780 WHEN MATCHED AND t.balance = 99 AND s.balance > 100 THEN
781 UPDATE SET balance = t.balance + s.balance;
782 SELECT * FROM wq_target;
783 tid | balance
784 -----+---------
785 1 | 99
786 (1 row)
788 MERGE INTO wq_target t
789 USING wq_source s ON t.tid = s.sid
790 WHEN MATCHED AND t.balance = 99 AND s.balance = 100 THEN
791 UPDATE SET balance = t.balance + s.balance;
792 SELECT * FROM wq_target;
793 tid | balance
794 -----+---------
795 1 | 199
796 (1 row)
798 -- check if OR works
799 MERGE INTO wq_target t
800 USING wq_source s ON t.tid = s.sid
801 WHEN MATCHED AND t.balance = 99 OR s.balance > 100 THEN
802 UPDATE SET balance = t.balance + s.balance;
803 SELECT * FROM wq_target;
804 tid | balance
805 -----+---------
806 1 | 199
807 (1 row)
809 MERGE INTO wq_target t
810 USING wq_source s ON t.tid = s.sid
811 WHEN MATCHED AND t.balance = 199 OR s.balance > 100 THEN
812 UPDATE SET balance = t.balance + s.balance;
813 SELECT * FROM wq_target;
814 tid | balance
815 -----+---------
816 1 | 299
817 (1 row)
819 -- check source-side whole-row references
820 BEGIN;
821 MERGE INTO wq_target t
822 USING wq_source s ON (t.tid = s.sid)
823 WHEN matched and t = s or t.tid = s.sid THEN
824 UPDATE SET balance = t.balance + s.balance;
825 SELECT * FROM wq_target;
826 tid | balance
827 -----+---------
828 1 | 399
829 (1 row)
831 ROLLBACK;
832 -- check if subqueries work in the conditions?
833 MERGE INTO wq_target t
834 USING wq_source s ON t.tid = s.sid
835 WHEN MATCHED AND t.balance > (SELECT max(balance) FROM target) THEN
836 UPDATE SET balance = t.balance + s.balance;
837 -- check if we can access system columns in the conditions
838 MERGE INTO wq_target t
839 USING wq_source s ON t.tid = s.sid
840 WHEN MATCHED AND t.xmin = t.xmax THEN
841 UPDATE SET balance = t.balance + s.balance;
842 ERROR: cannot use system column "xmin" in MERGE WHEN condition
843 LINE 3: WHEN MATCHED AND t.xmin = t.xmax THEN
844 ^
845 MERGE INTO wq_target t
846 USING wq_source s ON t.tid = s.sid
847 WHEN MATCHED AND t.tableoid >= 0 THEN
848 UPDATE SET balance = t.balance + s.balance;
849 SELECT * FROM wq_target;
850 tid | balance
851 -----+---------
852 1 | 499
853 (1 row)
855 DROP TABLE wq_target, wq_source;
856 -- test triggers
857 create or replace function merge_trigfunc () returns trigger
858 language plpgsql as
859 $$
860 DECLARE
861 line text;
862 BEGIN
863 SELECT INTO line format('%s %s %s trigger%s',
864 TG_WHEN, TG_OP, TG_LEVEL, CASE
865 WHEN TG_OP = 'INSERT' AND TG_LEVEL = 'ROW'
866 THEN format(' row: %s', NEW)
867 WHEN TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW'
868 THEN format(' row: %s -> %s', OLD, NEW)
869 WHEN TG_OP = 'DELETE' AND TG_LEVEL = 'ROW'
870 THEN format(' row: %s', OLD)
871 END);
873 RAISE NOTICE '%', line;
874 IF (TG_WHEN = 'BEFORE' AND TG_LEVEL = 'ROW') THEN
875 IF (TG_OP = 'DELETE') THEN
876 RETURN OLD;
877 ELSE
878 RETURN NEW;
879 END IF;
880 ELSE
881 RETURN NULL;
882 END IF;
883 END;
884 $$;
885 CREATE TRIGGER merge_bsi BEFORE INSERT ON target FOR EACH STATEMENT EXECUTE PROCEDURE merge_trigfunc ();
886 CREATE TRIGGER merge_bsu BEFORE UPDATE ON target FOR EACH STATEMENT EXECUTE PROCEDURE merge_trigfunc ();
887 CREATE TRIGGER merge_bsd BEFORE DELETE ON target FOR EACH STATEMENT EXECUTE PROCEDURE merge_trigfunc ();
888 CREATE TRIGGER merge_asi AFTER INSERT ON target FOR EACH STATEMENT EXECUTE PROCEDURE merge_trigfunc ();
889 CREATE TRIGGER merge_asu AFTER UPDATE ON target FOR EACH STATEMENT EXECUTE PROCEDURE merge_trigfunc ();
890 CREATE TRIGGER merge_asd AFTER DELETE ON target FOR EACH STATEMENT EXECUTE PROCEDURE merge_trigfunc ();
891 CREATE TRIGGER merge_bri BEFORE INSERT ON target FOR EACH ROW EXECUTE PROCEDURE merge_trigfunc ();
892 CREATE TRIGGER merge_bru BEFORE UPDATE ON target FOR EACH ROW EXECUTE PROCEDURE merge_trigfunc ();
893 CREATE TRIGGER merge_brd BEFORE DELETE ON target FOR EACH ROW EXECUTE PROCEDURE merge_trigfunc ();
894 CREATE TRIGGER merge_ari AFTER INSERT ON target FOR EACH ROW EXECUTE PROCEDURE merge_trigfunc ();
895 CREATE TRIGGER merge_aru AFTER UPDATE ON target FOR EACH ROW EXECUTE PROCEDURE merge_trigfunc ();
896 CREATE TRIGGER merge_ard AFTER DELETE ON target FOR EACH ROW EXECUTE PROCEDURE merge_trigfunc ();
897 -- now the classic UPSERT, with a DELETE
898 BEGIN;
899 UPDATE target SET balance = 0 WHERE tid = 3;
900 NOTICE: BEFORE UPDATE STATEMENT trigger
901 NOTICE: BEFORE UPDATE ROW trigger row: (3,30) -> (3,0)
902 NOTICE: AFTER UPDATE ROW trigger row: (3,30) -> (3,0)
903 NOTICE: AFTER UPDATE STATEMENT trigger
904 --EXPLAIN (ANALYZE ON, COSTS OFF, SUMMARY OFF, TIMING OFF)
905 MERGE INTO target t
906 USING source AS s
907 ON t.tid = s.sid
908 WHEN MATCHED AND t.balance > s.delta THEN
909 UPDATE SET balance = t.balance - s.delta
910 WHEN MATCHED THEN
911 DELETE
912 WHEN NOT MATCHED THEN
913 INSERT VALUES (s.sid, s.delta);
914 NOTICE: BEFORE INSERT STATEMENT trigger
915 NOTICE: BEFORE UPDATE STATEMENT trigger
916 NOTICE: BEFORE DELETE STATEMENT trigger
917 NOTICE: BEFORE DELETE ROW trigger row: (3,0)
918 NOTICE: BEFORE UPDATE ROW trigger row: (2,20) -> (2,15)
919 NOTICE: BEFORE INSERT ROW trigger row: (4,40)
920 NOTICE: AFTER DELETE ROW trigger row: (3,0)
921 NOTICE: AFTER UPDATE ROW trigger row: (2,20) -> (2,15)
922 NOTICE: AFTER INSERT ROW trigger row: (4,40)
923 NOTICE: AFTER DELETE STATEMENT trigger
924 NOTICE: AFTER UPDATE STATEMENT trigger
925 NOTICE: AFTER INSERT STATEMENT trigger
926 SELECT * FROM target ORDER BY tid;
927 tid | balance
928 -----+---------
929 1 | 10
930 2 | 15
931 4 | 40
932 (3 rows)
934 ROLLBACK;
935 -- Test behavior of triggers that turn UPDATE/DELETE into no-ops
936 create or replace function skip_merge_op() returns trigger
937 language plpgsql as
938 $$
939 BEGIN
940 RETURN NULL;
941 END;
942 $$;
943 SELECT * FROM target full outer join source on (sid = tid);
944 tid | balance | sid | delta
945 -----+---------+-----+-------
946 3 | 30 | 3 | 20
947 2 | 20 | 2 | 5
948 | | 4 | 40
949 1 | 10 | |
950 (4 rows)
952 create trigger merge_skip BEFORE INSERT OR UPDATE or DELETE
953 ON target FOR EACH ROW EXECUTE FUNCTION skip_merge_op();
954 DO $$
955 DECLARE
956 result integer;
957 BEGIN
958 MERGE INTO target t
959 USING source AS s
960 ON t.tid = s.sid
961 WHEN MATCHED AND s.sid = 3 THEN UPDATE SET balance = t.balance + s.delta
962 WHEN MATCHED THEN DELETE
963 WHEN NOT MATCHED THEN INSERT VALUES (sid, delta);
964 IF FOUND THEN
965 RAISE NOTICE 'Found';
966 ELSE
967 RAISE NOTICE 'Not found';
968 END IF;
969 GET DIAGNOSTICS result := ROW_COUNT;
970 RAISE NOTICE 'ROW_COUNT = %', result;
971 END;
972 $$;
973 NOTICE: BEFORE INSERT STATEMENT trigger
974 NOTICE: BEFORE UPDATE STATEMENT trigger
975 NOTICE: BEFORE DELETE STATEMENT trigger
976 NOTICE: BEFORE UPDATE ROW trigger row: (3,30) -> (3,50)
977 NOTICE: BEFORE DELETE ROW trigger row: (2,20)
978 NOTICE: BEFORE INSERT ROW trigger row: (4,40)
979 NOTICE: AFTER DELETE STATEMENT trigger
980 NOTICE: AFTER UPDATE STATEMENT trigger
981 NOTICE: AFTER INSERT STATEMENT trigger
982 NOTICE: Not found
983 NOTICE: ROW_COUNT = 0
984 SELECT * FROM target FULL OUTER JOIN source ON (sid = tid);
985 tid | balance | sid | delta
986 -----+---------+-----+-------
987 3 | 30 | 3 | 20
988 2 | 20 | 2 | 5
989 | | 4 | 40
990 1 | 10 | |
991 (4 rows)
993 DROP TRIGGER merge_skip ON target;
994 DROP FUNCTION skip_merge_op();
995 -- test from PL/pgSQL
996 -- make sure MERGE INTO isn't interpreted to mean returning variables like SELECT INTO
997 BEGIN;
998 DO LANGUAGE plpgsql $$
999 BEGIN
1000 MERGE INTO target t
1001 USING source AS s
1002 ON t.tid = s.sid
1003 WHEN MATCHED AND t.balance > s.delta THEN
1004 UPDATE SET balance = t.balance - s.delta;
1005 END;
1006 $$;
1007 NOTICE: BEFORE UPDATE STATEMENT trigger
1008 NOTICE: BEFORE UPDATE ROW trigger row: (3,30) -> (3,10)
1009 NOTICE: BEFORE UPDATE ROW trigger row: (2,20) -> (2,15)
1010 NOTICE: AFTER UPDATE ROW trigger row: (3,30) -> (3,10)
1011 NOTICE: AFTER UPDATE ROW trigger row: (2,20) -> (2,15)
1012 NOTICE: AFTER UPDATE STATEMENT trigger
1013 ROLLBACK;
1014 --source constants
1015 BEGIN;
1016 MERGE INTO target t
1017 USING (SELECT 9 AS sid, 57 AS delta) AS s
1018 ON t.tid = s.sid
1019 WHEN NOT MATCHED THEN
1020 INSERT (tid, balance) VALUES (s.sid, s.delta);
1021 NOTICE: BEFORE INSERT STATEMENT trigger
1022 NOTICE: BEFORE INSERT ROW trigger row: (9,57)
1023 NOTICE: AFTER INSERT ROW trigger row: (9,57)
1024 NOTICE: AFTER INSERT STATEMENT trigger
1025 SELECT * FROM target ORDER BY tid;
1026 tid | balance
1027 -----+---------
1028 1 | 10
1029 2 | 20
1030 3 | 30
1031 9 | 57
1032 (4 rows)
1034 ROLLBACK;
1035 --source query
1036 BEGIN;
1037 MERGE INTO target t
1038 USING (SELECT sid, delta FROM source WHERE delta > 0) AS s
1039 ON t.tid = s.sid
1040 WHEN NOT MATCHED THEN
1041 INSERT (tid, balance) VALUES (s.sid, s.delta);
1042 NOTICE: BEFORE INSERT STATEMENT trigger
1043 NOTICE: BEFORE INSERT ROW trigger row: (4,40)
1044 NOTICE: AFTER INSERT ROW trigger row: (4,40)
1045 NOTICE: AFTER INSERT STATEMENT trigger
1046 SELECT * FROM target ORDER BY tid;
1047 tid | balance
1048 -----+---------
1049 1 | 10
1050 2 | 20
1051 3 | 30
1052 4 | 40
1053 (4 rows)
1055 ROLLBACK;
1056 BEGIN;
1057 MERGE INTO target t
1058 USING (SELECT sid, delta as newname FROM source WHERE delta > 0) AS s
1059 ON t.tid = s.sid
1060 WHEN NOT MATCHED THEN
1061 INSERT (tid, balance) VALUES (s.sid, s.newname);
1062 NOTICE: BEFORE INSERT STATEMENT trigger
1063 NOTICE: BEFORE INSERT ROW trigger row: (4,40)
1064 NOTICE: AFTER INSERT ROW trigger row: (4,40)
1065 NOTICE: AFTER INSERT STATEMENT trigger
1066 SELECT * FROM target ORDER BY tid;
1067 tid | balance
1068 -----+---------
1069 1 | 10
1070 2 | 20
1071 3 | 30
1072 4 | 40
1073 (4 rows)
1075 ROLLBACK;
1076 --self-merge
1077 BEGIN;
1078 MERGE INTO target t1
1079 USING target t2
1080 ON t1.tid = t2.tid
1081 WHEN MATCHED THEN
1082 UPDATE SET balance = t1.balance + t2.balance
1083 WHEN NOT MATCHED THEN
1084 INSERT VALUES (t2.tid, t2.balance);
1085 NOTICE: BEFORE INSERT STATEMENT trigger
1086 NOTICE: BEFORE UPDATE STATEMENT trigger
1087 NOTICE: BEFORE UPDATE ROW trigger row: (1,10) -> (1,20)
1088 NOTICE: BEFORE UPDATE ROW trigger row: (2,20) -> (2,40)
1089 NOTICE: BEFORE UPDATE ROW trigger row: (3,30) -> (3,60)
1090 NOTICE: AFTER UPDATE ROW trigger row: (1,10) -> (1,20)
1091 NOTICE: AFTER UPDATE ROW trigger row: (2,20) -> (2,40)
1092 NOTICE: AFTER UPDATE ROW trigger row: (3,30) -> (3,60)
1093 NOTICE: AFTER UPDATE STATEMENT trigger
1094 NOTICE: AFTER INSERT STATEMENT trigger
1095 SELECT * FROM target ORDER BY tid;
1096 tid | balance
1097 -----+---------
1098 1 | 20
1099 2 | 40
1100 3 | 60
1101 (3 rows)
1103 ROLLBACK;
1104 BEGIN;
1105 MERGE INTO target t
1106 USING (SELECT tid as sid, balance as delta FROM target WHERE balance > 0) AS s
1107 ON t.tid = s.sid
1108 WHEN NOT MATCHED THEN
1109 INSERT (tid, balance) VALUES (s.sid, s.delta);
1110 NOTICE: BEFORE INSERT STATEMENT trigger
1111 NOTICE: AFTER INSERT STATEMENT trigger
1112 SELECT * FROM target ORDER BY tid;
1113 tid | balance
1114 -----+---------
1115 1 | 10
1116 2 | 20
1117 3 | 30
1118 (3 rows)
1120 ROLLBACK;
1121 BEGIN;
1122 MERGE INTO target t
1123 USING
1124 (SELECT sid, max(delta) AS delta
1125 FROM source
1126 GROUP BY sid
1127 HAVING count(*) = 1
1128 ORDER BY sid ASC) AS s
1129 ON t.tid = s.sid
1130 WHEN NOT MATCHED THEN
1131 INSERT (tid, balance) VALUES (s.sid, s.delta);
1132 NOTICE: BEFORE INSERT STATEMENT trigger
1133 NOTICE: BEFORE INSERT ROW trigger row: (4,40)
1134 NOTICE: AFTER INSERT ROW trigger row: (4,40)
1135 NOTICE: AFTER INSERT STATEMENT trigger
1136 SELECT * FROM target ORDER BY tid;
1137 tid | balance
1138 -----+---------
1139 1 | 10
1140 2 | 20
1141 3 | 30
1142 4 | 40
1143 (4 rows)
1145 ROLLBACK;
1146 -- plpgsql parameters and results
1147 BEGIN;
1148 CREATE FUNCTION merge_func (p_id integer, p_bal integer)
1149 RETURNS INTEGER
1150 LANGUAGE plpgsql
1151 AS $$
1152 DECLARE
1153 result integer;
1154 BEGIN
1155 MERGE INTO target t
1156 USING (SELECT p_id AS sid) AS s
1157 ON t.tid = s.sid
1158 WHEN MATCHED THEN
1159 UPDATE SET balance = t.balance - p_bal;
1160 IF FOUND THEN
1161 GET DIAGNOSTICS result := ROW_COUNT;
1162 END IF;
1163 RETURN result;
1164 END;
1165 $$;
1166 SELECT merge_func(3, 4);
1167 NOTICE: BEFORE UPDATE STATEMENT trigger
1168 NOTICE: BEFORE UPDATE ROW trigger row: (3,30) -> (3,26)
1169 NOTICE: AFTER UPDATE ROW trigger row: (3,30) -> (3,26)
1170 NOTICE: AFTER UPDATE STATEMENT trigger
1171 merge_func
1172 ------------
1173 1
1174 (1 row)
1176 SELECT * FROM target ORDER BY tid;
1177 tid | balance
1178 -----+---------
1179 1 | 10
1180 2 | 20
1181 3 | 26
1182 (3 rows)
1184 ROLLBACK;
1185 -- PREPARE
1186 BEGIN;
1187 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;
1188 execute foom;
1189 NOTICE: BEFORE UPDATE STATEMENT trigger
1190 NOTICE: BEFORE UPDATE ROW trigger row: (1,10) -> (1,1)
1191 NOTICE: AFTER UPDATE ROW trigger row: (1,10) -> (1,1)
1192 NOTICE: AFTER UPDATE STATEMENT trigger
1193 SELECT * FROM target ORDER BY tid;
1194 tid | balance
1195 -----+---------
1196 1 | 1
1197 2 | 20
1198 3 | 30
1199 (3 rows)
1201 ROLLBACK;
1202 BEGIN;
1203 PREPARE foom2 (integer, integer) AS
1204 MERGE INTO target t
1205 USING (SELECT 1) s
1206 ON t.tid = $1
1207 WHEN MATCHED THEN
1208 UPDATE SET balance = $2;
1209 --EXPLAIN (ANALYZE ON, COSTS OFF, SUMMARY OFF, TIMING OFF)
1210 execute foom2 (1, 1);
1211 NOTICE: BEFORE UPDATE STATEMENT trigger
1212 NOTICE: BEFORE UPDATE ROW trigger row: (1,10) -> (1,1)
1213 NOTICE: AFTER UPDATE ROW trigger row: (1,10) -> (1,1)
1214 NOTICE: AFTER UPDATE STATEMENT trigger
1215 SELECT * FROM target ORDER BY tid;
1216 tid | balance
1217 -----+---------
1218 1 | 1
1219 2 | 20
1220 3 | 30
1221 (3 rows)
1223 ROLLBACK;
1224 -- subqueries in source relation
1225 CREATE TABLE sq_target (tid integer NOT NULL, balance integer)
1226 WITH (autovacuum_enabled=off);
1227 CREATE TABLE sq_source (delta integer, sid integer, balance integer DEFAULT 0)
1228 WITH (autovacuum_enabled=off);
1229 INSERT INTO sq_target(tid, balance) VALUES (1,100), (2,200), (3,300);
1230 INSERT INTO sq_source(sid, delta) VALUES (1,10), (2,20), (4,40);
1231 BEGIN;
1232 MERGE INTO sq_target t
1233 USING (SELECT * FROM sq_source) s
1234 ON tid = sid
1235 WHEN MATCHED AND t.balance > delta THEN
1236 UPDATE SET balance = t.balance + delta;
1237 SELECT * FROM sq_target;
1238 tid | balance
1239 -----+---------
1240 3 | 300
1241 1 | 110
1242 2 | 220
1243 (3 rows)
1245 ROLLBACK;
1246 -- try a view
1247 CREATE VIEW v AS SELECT * FROM sq_source WHERE sid < 2;
1248 BEGIN;
1249 MERGE INTO sq_target
1250 USING v
1251 ON tid = sid
1252 WHEN MATCHED THEN
1253 UPDATE SET balance = v.balance + delta;
1254 SELECT * FROM sq_target;
1255 tid | balance
1256 -----+---------
1257 2 | 200
1258 3 | 300
1259 1 | 10
1260 (3 rows)
1262 ROLLBACK;
1263 -- ambiguous reference to a column
1264 BEGIN;
1265 MERGE INTO sq_target
1266 USING v
1267 ON tid = sid
1268 WHEN MATCHED AND tid > 2 THEN
1269 UPDATE SET balance = balance + delta
1270 WHEN NOT MATCHED THEN
1271 INSERT (balance, tid) VALUES (balance + delta, sid)
1272 WHEN MATCHED AND tid < 2 THEN
1273 DELETE;
1274 ERROR: column reference "balance" is ambiguous
1275 LINE 5: UPDATE SET balance = balance + delta
1276 ^
1277 ROLLBACK;
1278 BEGIN;
1279 INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10);
1280 MERGE INTO sq_target t
1281 USING v
1282 ON tid = sid
1283 WHEN MATCHED AND tid > 2 THEN
1284 UPDATE SET balance = t.balance + delta
1285 WHEN NOT MATCHED THEN
1286 INSERT (balance, tid) VALUES (balance + delta, sid)
1287 WHEN MATCHED AND tid < 2 THEN
1288 DELETE;
1289 SELECT * FROM sq_target;
1290 tid | balance
1291 -----+---------
1292 2 | 200
1293 3 | 300
1294 -1 | -11
1295 (3 rows)
1297 ROLLBACK;
1298 -- CTEs
1299 BEGIN;
1300 INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10);
1301 WITH targq AS (
1302 SELECT * FROM v
1303 )
1304 MERGE INTO sq_target t
1305 USING v
1306 ON tid = sid
1307 WHEN MATCHED AND tid > 2 THEN
1308 UPDATE SET balance = t.balance + delta
1309 WHEN NOT MATCHED THEN
1310 INSERT (balance, tid) VALUES (balance + delta, sid)
1311 WHEN MATCHED AND tid < 2 THEN
1312 DELETE;
1313 ROLLBACK;
1314 -- RETURNING
1315 BEGIN;
1316 INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10);
1317 MERGE INTO sq_target t
1318 USING v
1319 ON tid = sid
1320 WHEN MATCHED AND tid > 2 THEN
1321 UPDATE SET balance = t.balance + delta
1322 WHEN NOT MATCHED THEN
1323 INSERT (balance, tid) VALUES (balance + delta, sid)
1324 WHEN MATCHED AND tid < 2 THEN
1325 DELETE
1326 RETURNING *;
1327 ERROR: syntax error at or near "RETURNING"
1328 LINE 10: RETURNING *;
1329 ^
1330 ROLLBACK;
1331 -- EXPLAIN
1332 CREATE TABLE ex_mtarget (a int, b int)
1333 WITH (autovacuum_enabled=off);
1334 CREATE TABLE ex_msource (a int, b int)
1335 WITH (autovacuum_enabled=off);
1336 INSERT INTO ex_mtarget SELECT i, i*10 FROM generate_series(1,100,2) i;
1337 INSERT INTO ex_msource SELECT i, i*10 FROM generate_series(1,100,1) i;
1338 CREATE FUNCTION explain_merge(query text) RETURNS SETOF text
1339 LANGUAGE plpgsql AS
1340 $$
1341 DECLARE ln text;
1342 BEGIN
1343 FOR ln IN
1344 EXECUTE 'explain (analyze, timing off, summary off, costs off) ' ||
1345 query
1346 LOOP
1347 ln := regexp_replace(ln, '(Memory( Usage)?|Buckets|Batches): \S*', '\1: xxx', 'g');
1348 RETURN NEXT ln;
1349 END LOOP;
1350 END;
1351 $$;
1352 -- only updates
1353 SELECT explain_merge('
1354 MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
1355 WHEN MATCHED THEN
1356 UPDATE SET b = t.b + 1');
1357 explain_merge
1358 ----------------------------------------------------------------------
1359 Merge on ex_mtarget t (actual rows=0 loops=1)
1360 Tuples: updated=50
1361 -> Merge Join (actual rows=50 loops=1)
1362 Merge Cond: (t.a = s.a)
1363 -> Sort (actual rows=50 loops=1)
1364 Sort Key: t.a
1365 Sort Method: quicksort Memory: xxx
1366 -> Seq Scan on ex_mtarget t (actual rows=50 loops=1)
1367 -> Sort (actual rows=100 loops=1)
1368 Sort Key: s.a
1369 Sort Method: quicksort Memory: xxx
1370 -> Seq Scan on ex_msource s (actual rows=100 loops=1)
1371 (12 rows)
1373 -- only updates to selected tuples
1374 SELECT explain_merge('
1375 MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
1376 WHEN MATCHED AND t.a < 10 THEN
1377 UPDATE SET b = t.b + 1');
1378 explain_merge
1379 ----------------------------------------------------------------------
1380 Merge on ex_mtarget t (actual rows=0 loops=1)
1381 Tuples: updated=5 skipped=45
1382 -> Merge Join (actual rows=50 loops=1)
1383 Merge Cond: (t.a = s.a)
1384 -> Sort (actual rows=50 loops=1)
1385 Sort Key: t.a
1386 Sort Method: quicksort Memory: xxx
1387 -> Seq Scan on ex_mtarget t (actual rows=50 loops=1)
1388 -> Sort (actual rows=100 loops=1)
1389 Sort Key: s.a
1390 Sort Method: quicksort Memory: xxx
1391 -> Seq Scan on ex_msource s (actual rows=100 loops=1)
1392 (12 rows)
1394 -- updates + deletes
1395 SELECT explain_merge('
1396 MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
1397 WHEN MATCHED AND t.a < 10 THEN
1398 UPDATE SET b = t.b + 1
1399 WHEN MATCHED AND t.a >= 10 AND t.a <= 20 THEN
1400 DELETE');
1401 explain_merge
1402 ----------------------------------------------------------------------
1403 Merge on ex_mtarget t (actual rows=0 loops=1)
1404 Tuples: updated=5 deleted=5 skipped=40
1405 -> Merge Join (actual rows=50 loops=1)
1406 Merge Cond: (t.a = s.a)
1407 -> Sort (actual rows=50 loops=1)
1408 Sort Key: t.a
1409 Sort Method: quicksort Memory: xxx
1410 -> Seq Scan on ex_mtarget t (actual rows=50 loops=1)
1411 -> Sort (actual rows=100 loops=1)
1412 Sort Key: s.a
1413 Sort Method: quicksort Memory: xxx
1414 -> Seq Scan on ex_msource s (actual rows=100 loops=1)
1415 (12 rows)
1417 -- only inserts
1418 SELECT explain_merge('
1419 MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
1420 WHEN NOT MATCHED AND s.a < 10 THEN
1421 INSERT VALUES (a, b)');
1422 explain_merge
1423 ----------------------------------------------------------------------
1424 Merge on ex_mtarget t (actual rows=0 loops=1)
1425 Tuples: inserted=4 skipped=96
1426 -> Merge Left Join (actual rows=100 loops=1)
1427 Merge Cond: (s.a = t.a)
1428 -> Sort (actual rows=100 loops=1)
1429 Sort Key: s.a
1430 Sort Method: quicksort Memory: xxx
1431 -> Seq Scan on ex_msource s (actual rows=100 loops=1)
1432 -> Sort (actual rows=45 loops=1)
1433 Sort Key: t.a
1434 Sort Method: quicksort Memory: xxx
1435 -> Seq Scan on ex_mtarget t (actual rows=45 loops=1)
1436 (12 rows)
1438 -- all three
1439 SELECT explain_merge('
1440 MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
1441 WHEN MATCHED AND t.a < 10 THEN
1442 UPDATE SET b = t.b + 1
1443 WHEN MATCHED AND t.a >= 30 AND t.a <= 40 THEN
1444 DELETE
1445 WHEN NOT MATCHED AND s.a < 20 THEN
1446 INSERT VALUES (a, b)');
1447 explain_merge
1448 ----------------------------------------------------------------------
1449 Merge on ex_mtarget t (actual rows=0 loops=1)
1450 Tuples: inserted=10 updated=9 deleted=5 skipped=76
1451 -> Merge Left Join (actual rows=100 loops=1)
1452 Merge Cond: (s.a = t.a)
1453 -> Sort (actual rows=100 loops=1)
1454 Sort Key: s.a
1455 Sort Method: quicksort Memory: xxx
1456 -> Seq Scan on ex_msource s (actual rows=100 loops=1)
1457 -> Sort (actual rows=49 loops=1)
1458 Sort Key: t.a
1459 Sort Method: quicksort Memory: xxx
1460 -> Seq Scan on ex_mtarget t (actual rows=49 loops=1)
1461 (12 rows)
1463 -- nothing
1464 SELECT explain_merge('
1465 MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a AND t.a < -1000
1466 WHEN MATCHED AND t.a < 10 THEN
1467 DO NOTHING');
1468 explain_merge
1469 --------------------------------------------------------------------
1470 Merge on ex_mtarget t (actual rows=0 loops=1)
1471 -> Merge Join (actual rows=0 loops=1)
1472 Merge Cond: (t.a = s.a)
1473 -> Sort (actual rows=0 loops=1)
1474 Sort Key: t.a
1475 Sort Method: quicksort Memory: xxx
1476 -> Seq Scan on ex_mtarget t (actual rows=0 loops=1)
1477 Filter: (a < '-1000'::integer)
1478 Rows Removed by Filter: 54
1479 -> Sort (never executed)
1480 Sort Key: s.a
1481 -> Seq Scan on ex_msource s (never executed)
1482 (12 rows)
1484 DROP TABLE ex_msource, ex_mtarget;
1485 DROP FUNCTION explain_merge(text);
1486 -- Subqueries
1487 BEGIN;
1488 MERGE INTO sq_target t
1489 USING v
1490 ON tid = sid
1491 WHEN MATCHED THEN
1492 UPDATE SET balance = (SELECT count(*) FROM sq_target);
1493 SELECT * FROM sq_target WHERE tid = 1;
1494 tid | balance
1495 -----+---------
1496 1 | 3
1497 (1 row)
1499 ROLLBACK;
1500 BEGIN;
1501 MERGE INTO sq_target t
1502 USING v
1503 ON tid = sid
1504 WHEN MATCHED AND (SELECT count(*) > 0 FROM sq_target) THEN
1505 UPDATE SET balance = 42;
1506 SELECT * FROM sq_target WHERE tid = 1;
1507 tid | balance
1508 -----+---------
1509 1 | 42
1510 (1 row)
1512 ROLLBACK;
1513 BEGIN;
1514 MERGE INTO sq_target t
1515 USING v
1516 ON tid = sid AND (SELECT count(*) > 0 FROM sq_target)
1517 WHEN MATCHED THEN
1518 UPDATE SET balance = 42;
1519 SELECT * FROM sq_target WHERE tid = 1;
1520 tid | balance
1521 -----+---------
1522 1 | 42
1523 (1 row)
1525 ROLLBACK;
1526 DROP TABLE sq_target, sq_source CASCADE;
1527 NOTICE: drop cascades to view v
1528 CREATE TABLE pa_target (tid integer, balance float, val text)
1529 PARTITION BY LIST (tid);
1530 CREATE TABLE part1 PARTITION OF pa_target FOR VALUES IN (1,4)
1531 WITH (autovacuum_enabled=off);
1532 CREATE TABLE part2 PARTITION OF pa_target FOR VALUES IN (2,5,6)
1533 WITH (autovacuum_enabled=off);
1534 CREATE TABLE part3 PARTITION OF pa_target FOR VALUES IN (3,8,9)
1535 WITH (autovacuum_enabled=off);
1536 CREATE TABLE part4 PARTITION OF pa_target DEFAULT
1537 WITH (autovacuum_enabled=off);
1538 CREATE TABLE pa_source (sid integer, delta float);
1539 -- insert many rows to the source table
1540 INSERT INTO pa_source SELECT id, id * 10 FROM generate_series(1,14) AS id;
1541 -- insert a few rows in the target table (odd numbered tid)
1542 INSERT INTO pa_target SELECT id, id * 100, 'initial' FROM generate_series(1,14,2) AS id;
1543 -- try simple MERGE
1544 BEGIN;
1545 MERGE INTO pa_target t
1546 USING pa_source s
1547 ON t.tid = s.sid
1548 WHEN MATCHED THEN
1549 UPDATE SET balance = balance + delta, val = val || ' updated by merge'
1550 WHEN NOT MATCHED THEN
1551 INSERT VALUES (sid, delta, 'inserted by merge');
1552 SELECT * FROM pa_target ORDER BY tid;
1553 tid | balance | val
1554 -----+---------+--------------------------
1555 1 | 110 | initial updated by merge
1556 2 | 20 | inserted by merge
1557 3 | 330 | initial updated by merge
1558 4 | 40 | inserted by merge
1559 5 | 550 | initial updated by merge
1560 6 | 60 | inserted by merge
1561 7 | 770 | initial updated by merge
1562 8 | 80 | inserted by merge
1563 9 | 990 | initial updated by merge
1564 10 | 100 | inserted by merge
1565 11 | 1210 | initial updated by merge
1566 12 | 120 | inserted by merge
1567 13 | 1430 | initial updated by merge
1568 14 | 140 | inserted by merge
1569 (14 rows)
1571 ROLLBACK;
1572 -- same with a constant qual
1573 BEGIN;
1574 MERGE INTO pa_target t
1575 USING pa_source s
1576 ON t.tid = s.sid AND tid = 1
1577 WHEN MATCHED THEN
1578 UPDATE SET balance = balance + delta, val = val || ' updated by merge'
1579 WHEN NOT MATCHED THEN
1580 INSERT VALUES (sid, delta, 'inserted by merge');
1581 SELECT * FROM pa_target ORDER BY tid;
1582 tid | balance | val
1583 -----+---------+--------------------------
1584 1 | 110 | initial updated by merge
1585 2 | 20 | inserted by merge
1586 3 | 30 | inserted by merge
1587 3 | 300 | initial
1588 4 | 40 | inserted by merge
1589 5 | 500 | initial
1590 5 | 50 | inserted by merge
1591 6 | 60 | inserted by merge
1592 7 | 700 | initial
1593 7 | 70 | inserted by merge
1594 8 | 80 | inserted by merge
1595 9 | 90 | inserted by merge
1596 9 | 900 | initial
1597 10 | 100 | inserted by merge
1598 11 | 1100 | initial
1599 11 | 110 | inserted by merge
1600 12 | 120 | inserted by merge
1601 13 | 1300 | initial
1602 13 | 130 | inserted by merge
1603 14 | 140 | inserted by merge
1604 (20 rows)
1606 ROLLBACK;
1607 -- try updating the partition key column
1608 BEGIN;
1609 CREATE FUNCTION merge_func() RETURNS integer LANGUAGE plpgsql AS $$
1610 DECLARE
1611 result integer;
1612 BEGIN
1613 MERGE INTO pa_target t
1614 USING pa_source s
1615 ON t.tid = s.sid
1616 WHEN MATCHED THEN
1617 UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge'
1618 WHEN NOT MATCHED THEN
1619 INSERT VALUES (sid, delta, 'inserted by merge');
1620 IF FOUND THEN
1621 GET DIAGNOSTICS result := ROW_COUNT;
1622 END IF;
1623 RETURN result;
1624 END;
1625 $$;
1626 SELECT merge_func();
1627 merge_func
1628 ------------
1629 14
1630 (1 row)
1632 SELECT * FROM pa_target ORDER BY tid;
1633 tid | balance | val
1634 -----+---------+--------------------------
1635 2 | 110 | initial updated by merge
1636 2 | 20 | inserted by merge
1637 4 | 40 | inserted by merge
1638 4 | 330 | initial updated by merge
1639 6 | 550 | initial updated by merge
1640 6 | 60 | inserted by merge
1641 8 | 80 | inserted by merge
1642 8 | 770 | initial updated by merge
1643 10 | 990 | initial updated by merge
1644 10 | 100 | inserted by merge
1645 12 | 1210 | initial updated by merge
1646 12 | 120 | inserted by merge
1647 14 | 1430 | initial updated by merge
1648 14 | 140 | inserted by merge
1649 (14 rows)
1651 ROLLBACK;
1652 DROP TABLE pa_target CASCADE;
1653 -- The target table is partitioned in the same way, but this time by attaching
1654 -- partitions which have columns in different order, dropped columns etc.
1655 CREATE TABLE pa_target (tid integer, balance float, val text)
1656 PARTITION BY LIST (tid);
1657 CREATE TABLE part1 (tid integer, balance float, val text)
1658 WITH (autovacuum_enabled=off);
1659 CREATE TABLE part2 (balance float, tid integer, val text)
1660 WITH (autovacuum_enabled=off);
1661 CREATE TABLE part3 (tid integer, balance float, val text)
1662 WITH (autovacuum_enabled=off);
1663 CREATE TABLE part4 (extraid text, tid integer, balance float, val text)
1664 WITH (autovacuum_enabled=off);
1665 ALTER TABLE part4 DROP COLUMN extraid;
1666 ALTER TABLE pa_target ATTACH PARTITION part1 FOR VALUES IN (1,4);
1667 ALTER TABLE pa_target ATTACH PARTITION part2 FOR VALUES IN (2,5,6);
1668 ALTER TABLE pa_target ATTACH PARTITION part3 FOR VALUES IN (3,8,9);
1669 ALTER TABLE pa_target ATTACH PARTITION part4 DEFAULT;
1670 -- insert a few rows in the target table (odd numbered tid)
1671 INSERT INTO pa_target SELECT id, id * 100, 'initial' FROM generate_series(1,14,2) AS id;
1672 -- try simple MERGE
1673 BEGIN;
1674 DO $$
1675 DECLARE
1676 result integer;
1677 BEGIN
1678 MERGE INTO pa_target t
1679 USING pa_source s
1680 ON t.tid = s.sid
1681 WHEN MATCHED THEN
1682 UPDATE SET balance = balance + delta, val = val || ' updated by merge'
1683 WHEN NOT MATCHED THEN
1684 INSERT VALUES (sid, delta, 'inserted by merge');
1685 GET DIAGNOSTICS result := ROW_COUNT;
1686 RAISE NOTICE 'ROW_COUNT = %', result;
1687 END;
1688 $$;
1689 NOTICE: ROW_COUNT = 14
1690 SELECT * FROM pa_target ORDER BY tid;
1691 tid | balance | val
1692 -----+---------+--------------------------
1693 1 | 110 | initial updated by merge
1694 2 | 20 | inserted by merge
1695 3 | 330 | initial updated by merge
1696 4 | 40 | inserted by merge
1697 5 | 550 | initial updated by merge
1698 6 | 60 | inserted by merge
1699 7 | 770 | initial updated by merge
1700 8 | 80 | inserted by merge
1701 9 | 990 | initial updated by merge
1702 10 | 100 | inserted by merge
1703 11 | 1210 | initial updated by merge
1704 12 | 120 | inserted by merge
1705 13 | 1430 | initial updated by merge
1706 14 | 140 | inserted by merge
1707 (14 rows)
1709 ROLLBACK;
1710 -- same with a constant qual
1711 BEGIN;
1712 MERGE INTO pa_target t
1713 USING pa_source s
1714 ON t.tid = s.sid AND tid IN (1, 5)
1715 WHEN MATCHED AND tid % 5 = 0 THEN DELETE
1716 WHEN MATCHED THEN
1717 UPDATE SET balance = balance + delta, val = val || ' updated by merge'
1718 WHEN NOT MATCHED THEN
1719 INSERT VALUES (sid, delta, 'inserted by merge');
1720 SELECT * FROM pa_target ORDER BY tid;
1721 tid | balance | val
1722 -----+---------+--------------------------
1723 1 | 110 | initial updated by merge
1724 2 | 20 | inserted by merge
1725 3 | 30 | inserted by merge
1726 3 | 300 | initial
1727 4 | 40 | inserted by merge
1728 6 | 60 | inserted by merge
1729 7 | 700 | initial
1730 7 | 70 | inserted by merge
1731 8 | 80 | inserted by merge
1732 9 | 900 | initial
1733 9 | 90 | inserted by merge
1734 10 | 100 | inserted by merge
1735 11 | 110 | inserted by merge
1736 11 | 1100 | initial
1737 12 | 120 | inserted by merge
1738 13 | 1300 | initial
1739 13 | 130 | inserted by merge
1740 14 | 140 | inserted by merge
1741 (18 rows)
1743 ROLLBACK;
1744 -- try updating the partition key column
1745 BEGIN;
1746 DO $$
1747 DECLARE
1748 result integer;
1749 BEGIN
1750 MERGE INTO pa_target t
1751 USING pa_source s
1752 ON t.tid = s.sid
1753 WHEN MATCHED THEN
1754 UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge'
1755 WHEN NOT MATCHED THEN
1756 INSERT VALUES (sid, delta, 'inserted by merge');
1757 GET DIAGNOSTICS result := ROW_COUNT;
1758 RAISE NOTICE 'ROW_COUNT = %', result;
1759 END;
1760 $$;
1761 NOTICE: ROW_COUNT = 14
1762 SELECT * FROM pa_target ORDER BY tid;
1763 tid | balance | val
1764 -----+---------+--------------------------
1765 2 | 110 | initial updated by merge
1766 2 | 20 | inserted by merge
1767 4 | 40 | inserted by merge
1768 4 | 330 | initial updated by merge
1769 6 | 550 | initial updated by merge
1770 6 | 60 | inserted by merge
1771 8 | 80 | inserted by merge
1772 8 | 770 | initial updated by merge
1773 10 | 990 | initial updated by merge
1774 10 | 100 | inserted by merge
1775 12 | 1210 | initial updated by merge
1776 12 | 120 | inserted by merge
1777 14 | 1430 | initial updated by merge
1778 14 | 140 | inserted by merge
1779 (14 rows)
1781 ROLLBACK;
1782 -- as above, but blocked by BEFORE DELETE ROW trigger
1783 BEGIN;
1784 CREATE FUNCTION trig_fn() RETURNS trigger LANGUAGE plpgsql AS
1785 $$ BEGIN RETURN NULL; END; $$;
1786 CREATE TRIGGER del_trig BEFORE DELETE ON pa_target
1787 FOR EACH ROW EXECUTE PROCEDURE trig_fn();
1788 DO $$
1789 DECLARE
1790 result integer;
1791 BEGIN
1792 MERGE INTO pa_target t
1793 USING pa_source s
1794 ON t.tid = s.sid
1795 WHEN MATCHED THEN
1796 UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge'
1797 WHEN NOT MATCHED THEN
1798 INSERT VALUES (sid, delta, 'inserted by merge');
1799 GET DIAGNOSTICS result := ROW_COUNT;
1800 RAISE NOTICE 'ROW_COUNT = %', result;
1801 END;
1802 $$;
1803 NOTICE: ROW_COUNT = 10
1804 SELECT * FROM pa_target ORDER BY tid;
1805 tid | balance | val
1806 -----+---------+--------------------------
1807 1 | 100 | initial
1808 2 | 20 | inserted by merge
1809 3 | 300 | initial
1810 4 | 40 | inserted by merge
1811 6 | 550 | initial updated by merge
1812 6 | 60 | inserted by merge
1813 7 | 700 | initial
1814 8 | 80 | inserted by merge
1815 9 | 900 | initial
1816 10 | 100 | inserted by merge
1817 12 | 1210 | initial updated by merge
1818 12 | 120 | inserted by merge
1819 14 | 1430 | initial updated by merge
1820 14 | 140 | inserted by merge
1821 (14 rows)
1823 ROLLBACK;
1824 -- as above, but blocked by BEFORE INSERT ROW trigger
1825 BEGIN;
1826 CREATE FUNCTION trig_fn() RETURNS trigger LANGUAGE plpgsql AS
1827 $$ BEGIN RETURN NULL; END; $$;
1828 CREATE TRIGGER ins_trig BEFORE INSERT ON pa_target
1829 FOR EACH ROW EXECUTE PROCEDURE trig_fn();
1830 DO $$
1831 DECLARE
1832 result integer;
1833 BEGIN
1834 MERGE INTO pa_target t
1835 USING pa_source s
1836 ON t.tid = s.sid
1837 WHEN MATCHED THEN
1838 UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge'
1839 WHEN NOT MATCHED THEN
1840 INSERT VALUES (sid, delta, 'inserted by merge');
1841 GET DIAGNOSTICS result := ROW_COUNT;
1842 RAISE NOTICE 'ROW_COUNT = %', result;
1843 END;
1844 $$;
1845 NOTICE: ROW_COUNT = 3
1846 SELECT * FROM pa_target ORDER BY tid;
1847 tid | balance | val
1848 -----+---------+--------------------------
1849 6 | 550 | initial updated by merge
1850 12 | 1210 | initial updated by merge
1851 14 | 1430 | initial updated by merge
1852 (3 rows)
1854 ROLLBACK;
1855 -- test RLS enforcement
1856 BEGIN;
1857 ALTER TABLE pa_target ENABLE ROW LEVEL SECURITY;
1858 ALTER TABLE pa_target FORCE ROW LEVEL SECURITY;
1859 CREATE POLICY pa_target_pol ON pa_target USING (tid != 0);
1860 MERGE INTO pa_target t
1861 USING pa_source s
1862 ON t.tid = s.sid AND t.tid IN (1,2,3,4)
1863 WHEN MATCHED THEN
1864 UPDATE SET tid = tid - 1;
1865 ERROR: new row violates row-level security policy for table "pa_target"
1866 ROLLBACK;
1867 DROP TABLE pa_source;
1868 DROP TABLE pa_target CASCADE;
1869 -- Sub-partitioning
1870 CREATE TABLE pa_target (logts timestamp, tid integer, balance float, val text)
1871 PARTITION BY RANGE (logts);
1872 CREATE TABLE part_m01 PARTITION OF pa_target
1873 FOR VALUES FROM ('2017-01-01') TO ('2017-02-01')
1874 PARTITION BY LIST (tid);
1875 CREATE TABLE part_m01_odd PARTITION OF part_m01
1876 FOR VALUES IN (1,3,5,7,9) WITH (autovacuum_enabled=off);
1877 CREATE TABLE part_m01_even PARTITION OF part_m01
1878 FOR VALUES IN (2,4,6,8) WITH (autovacuum_enabled=off);
1879 CREATE TABLE part_m02 PARTITION OF pa_target
1880 FOR VALUES FROM ('2017-02-01') TO ('2017-03-01')
1881 PARTITION BY LIST (tid);
1882 CREATE TABLE part_m02_odd PARTITION OF part_m02
1883 FOR VALUES IN (1,3,5,7,9) WITH (autovacuum_enabled=off);
1884 CREATE TABLE part_m02_even PARTITION OF part_m02
1885 FOR VALUES IN (2,4,6,8) WITH (autovacuum_enabled=off);
1886 CREATE TABLE pa_source (sid integer, delta float)
1887 WITH (autovacuum_enabled=off);
1888 -- insert many rows to the source table
1889 INSERT INTO pa_source SELECT id, id * 10 FROM generate_series(1,14) AS id;
1890 -- insert a few rows in the target table (odd numbered tid)
1891 INSERT INTO pa_target SELECT '2017-01-31', id, id * 100, 'initial' FROM generate_series(1,9,3) AS id;
1892 INSERT INTO pa_target SELECT '2017-02-28', id, id * 100, 'initial' FROM generate_series(2,9,3) AS id;
1893 -- try simple MERGE
1894 BEGIN;
1895 MERGE INTO pa_target t
1896 USING (SELECT '2017-01-15' AS slogts, * FROM pa_source WHERE sid < 10) s
1897 ON t.tid = s.sid
1898 WHEN MATCHED THEN
1899 UPDATE SET balance = balance + delta, val = val || ' updated by merge'
1900 WHEN NOT MATCHED THEN
1901 INSERT VALUES (slogts::timestamp, sid, delta, 'inserted by merge');
1902 SELECT * FROM pa_target ORDER BY tid;
1903 logts | tid | balance | val
1904 --------------------------+-----+---------+--------------------------
1905 Tue Jan 31 00:00:00 2017 | 1 | 110 | initial updated by merge
1906 Tue Feb 28 00:00:00 2017 | 2 | 220 | initial updated by merge
1907 Sun Jan 15 00:00:00 2017 | 3 | 30 | inserted by merge
1908 Tue Jan 31 00:00:00 2017 | 4 | 440 | initial updated by merge
1909 Tue Feb 28 00:00:00 2017 | 5 | 550 | initial updated by merge
1910 Sun Jan 15 00:00:00 2017 | 6 | 60 | inserted by merge
1911 Tue Jan 31 00:00:00 2017 | 7 | 770 | initial updated by merge
1912 Tue Feb 28 00:00:00 2017 | 8 | 880 | initial updated by merge
1913 Sun Jan 15 00:00:00 2017 | 9 | 90 | inserted by merge
1914 (9 rows)
1916 ROLLBACK;
1917 DROP TABLE pa_source;
1918 DROP TABLE pa_target CASCADE;
1919 -- Partitioned table with primary key
1920 CREATE TABLE pa_target (tid integer PRIMARY KEY) PARTITION BY LIST (tid);
1921 CREATE TABLE pa_targetp PARTITION OF pa_target DEFAULT;
1922 CREATE TABLE pa_source (sid integer);
1923 INSERT INTO pa_source VALUES (1), (2);
1924 EXPLAIN (VERBOSE, COSTS OFF)
1925 MERGE INTO pa_target t USING pa_source s ON t.tid = s.sid
1926 WHEN NOT MATCHED THEN INSERT VALUES (s.sid);
1927 QUERY PLAN
1928 -------------------------------------------------------------
1929 Merge on public.pa_target t
1930 Merge on public.pa_targetp t_1
1931 -> Hash Left Join
1932 Output: s.sid, s.ctid, t_1.tableoid, t_1.ctid
1933 Inner Unique: true
1934 Hash Cond: (s.sid = t_1.tid)
1935 -> Seq Scan on public.pa_source s
1936 Output: s.sid, s.ctid
1937 -> Hash
1938 Output: t_1.tid, t_1.tableoid, t_1.ctid
1939 -> Seq Scan on public.pa_targetp t_1
1940 Output: t_1.tid, t_1.tableoid, t_1.ctid
1941 (12 rows)
1943 MERGE INTO pa_target t USING pa_source s ON t.tid = s.sid
1944 WHEN NOT MATCHED THEN INSERT VALUES (s.sid);
1945 TABLE pa_target;
1946 tid
1947 -----
1948 1
1949 2
1950 (2 rows)
1952 -- Partition-less partitioned table
1953 -- (the bug we are checking for appeared only if table had partitions before)
1954 DROP TABLE pa_targetp;
1955 EXPLAIN (VERBOSE, COSTS OFF)
1956 MERGE INTO pa_target t USING pa_source s ON t.tid = s.sid
1957 WHEN NOT MATCHED THEN INSERT VALUES (s.sid);
1958 QUERY PLAN
1959 --------------------------------------------
1960 Merge on public.pa_target t
1961 -> Hash Left Join
1962 Output: s.sid, s.ctid, t.ctid
1963 Inner Unique: true
1964 Hash Cond: (s.sid = t.tid)
1965 -> Seq Scan on public.pa_source s
1966 Output: s.sid, s.ctid
1967 -> Hash
1968 Output: t.tid, t.ctid
1969 -> Result
1970 Output: t.tid, t.ctid
1971 One-Time Filter: false
1972 (12 rows)
1974 MERGE INTO pa_target t USING pa_source s ON t.tid = s.sid
1975 WHEN NOT MATCHED THEN INSERT VALUES (s.sid);
1976 ERROR: no partition of relation "pa_target" found for row
1977 DETAIL: Partition key of the failing row contains (tid) = (1).
1978 DROP TABLE pa_source;
1979 DROP TABLE pa_target CASCADE;
1980 -- some complex joins on the source side
1981 CREATE TABLE cj_target (tid integer, balance float, val text)
1982 WITH (autovacuum_enabled=off);
1983 CREATE TABLE cj_source1 (sid1 integer, scat integer, delta integer)
1984 WITH (autovacuum_enabled=off);
1985 CREATE TABLE cj_source2 (sid2 integer, sval text)
1986 WITH (autovacuum_enabled=off);
1987 INSERT INTO cj_source1 VALUES (1, 10, 100);
1988 INSERT INTO cj_source1 VALUES (1, 20, 200);
1989 INSERT INTO cj_source1 VALUES (2, 20, 300);
1990 INSERT INTO cj_source1 VALUES (3, 10, 400);
1991 INSERT INTO cj_source2 VALUES (1, 'initial source2');
1992 INSERT INTO cj_source2 VALUES (2, 'initial source2');
1993 INSERT INTO cj_source2 VALUES (3, 'initial source2');
1994 -- source relation is an unaliased join
1995 MERGE INTO cj_target t
1996 USING cj_source1 s1
1997 INNER JOIN cj_source2 s2 ON sid1 = sid2
1998 ON t.tid = sid1
1999 WHEN NOT MATCHED THEN
2000 INSERT VALUES (sid1, delta, sval);
2001 -- try accessing columns from either side of the source join
2002 MERGE INTO cj_target t
2003 USING cj_source2 s2
2004 INNER JOIN cj_source1 s1 ON sid1 = sid2 AND scat = 20
2005 ON t.tid = sid1
2006 WHEN NOT MATCHED THEN
2007 INSERT VALUES (sid2, delta, sval)
2008 WHEN MATCHED THEN
2009 DELETE;
2010 -- some simple expressions in INSERT targetlist
2011 MERGE INTO cj_target t
2012 USING cj_source2 s2
2013 INNER JOIN cj_source1 s1 ON sid1 = sid2
2014 ON t.tid = sid1
2015 WHEN NOT MATCHED THEN
2016 INSERT VALUES (sid2, delta + scat, sval)
2017 WHEN MATCHED THEN
2018 UPDATE SET val = val || ' updated by merge';
2019 MERGE INTO cj_target t
2020 USING cj_source2 s2
2021 INNER JOIN cj_source1 s1 ON sid1 = sid2 AND scat = 20
2022 ON t.tid = sid1
2023 WHEN MATCHED THEN
2024 UPDATE SET val = val || ' ' || delta::text;
2025 SELECT * FROM cj_target;
2026 tid | balance | val
2027 -----+---------+----------------------------------
2028 3 | 400 | initial source2 updated by merge
2029 1 | 220 | initial source2 200
2030 1 | 110 | initial source2 200
2031 2 | 320 | initial source2 300
2032 (4 rows)
2034 -- try it with an outer join and PlaceHolderVar
2035 MERGE INTO cj_target t
2036 USING (SELECT *, 'join input'::text AS phv FROM cj_source1) fj
2037 FULL JOIN cj_source2 fj2 ON fj.scat = fj2.sid2 * 10
2038 ON t.tid = fj.scat
2039 WHEN NOT MATCHED THEN
2040 INSERT (tid, balance, val) VALUES (fj.scat, fj.delta, fj.phv);
2041 SELECT * FROM cj_target;
2042 tid | balance | val
2043 -----+---------+----------------------------------
2044 3 | 400 | initial source2 updated by merge
2045 1 | 220 | initial source2 200
2046 1 | 110 | initial source2 200
2047 2 | 320 | initial source2 300
2048 10 | 100 | join input
2049 10 | 400 | join input
2050 20 | 200 | join input
2051 20 | 300 | join input
2052 | |
2053 (9 rows)
2055 ALTER TABLE cj_source1 RENAME COLUMN sid1 TO sid;
2056 ALTER TABLE cj_source2 RENAME COLUMN sid2 TO sid;
2057 TRUNCATE cj_target;
2058 MERGE INTO cj_target t
2059 USING cj_source1 s1
2060 INNER JOIN cj_source2 s2 ON s1.sid = s2.sid
2061 ON t.tid = s1.sid
2062 WHEN NOT MATCHED THEN
2063 INSERT VALUES (s2.sid, delta, sval);
2064 DROP TABLE cj_source2, cj_source1, cj_target;
2065 -- Function scans
2066 CREATE TABLE fs_target (a int, b int, c text)
2067 WITH (autovacuum_enabled=off);
2068 MERGE INTO fs_target t
2069 USING generate_series(1,100,1) AS id
2070 ON t.a = id
2071 WHEN MATCHED THEN
2072 UPDATE SET b = b + id
2073 WHEN NOT MATCHED THEN
2074 INSERT VALUES (id, -1);
2075 MERGE INTO fs_target t
2076 USING generate_series(1,100,2) AS id
2077 ON t.a = id
2078 WHEN MATCHED THEN
2079 UPDATE SET b = b + id, c = 'updated '|| id.*::text
2080 WHEN NOT MATCHED THEN
2081 INSERT VALUES (id, -1, 'inserted ' || id.*::text);
2082 SELECT count(*) FROM fs_target;
2083 count
2084 -------
2085 100
2086 (1 row)
2088 DROP TABLE fs_target;
2089 -- SERIALIZABLE test
2090 -- handled in isolation tests
2091 -- Inheritance-based partitioning
2092 CREATE TABLE measurement (
2093 city_id int not null,
2094 logdate date not null,
2095 peaktemp int,
2096 unitsales int
2097 ) WITH (autovacuum_enabled=off);
2098 CREATE TABLE measurement_y2006m02 (
2099 CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
2100 ) INHERITS (measurement) WITH (autovacuum_enabled=off);
2101 CREATE TABLE measurement_y2006m03 (
2102 CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
2103 ) INHERITS (measurement) WITH (autovacuum_enabled=off);
2104 CREATE TABLE measurement_y2007m01 (
2105 filler text,
2106 peaktemp int,
2107 logdate date not null,
2108 city_id int not null,
2109 unitsales int
2110 CHECK ( logdate >= DATE '2007-01-01' AND logdate < DATE '2007-02-01')
2111 ) WITH (autovacuum_enabled=off);
2112 ALTER TABLE measurement_y2007m01 DROP COLUMN filler;
2113 ALTER TABLE measurement_y2007m01 INHERIT measurement;
2114 INSERT INTO measurement VALUES (0, '2005-07-21', 5, 15);
2115 CREATE OR REPLACE FUNCTION measurement_insert_trigger()
2116 RETURNS TRIGGER AS $$
2117 BEGIN
2118 IF ( NEW.logdate >= DATE '2006-02-01' AND
2119 NEW.logdate < DATE '2006-03-01' ) THEN
2120 INSERT INTO measurement_y2006m02 VALUES (NEW.*);
2121 ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
2122 NEW.logdate < DATE '2006-04-01' ) THEN
2123 INSERT INTO measurement_y2006m03 VALUES (NEW.*);
2124 ELSIF ( NEW.logdate >= DATE '2007-01-01' AND
2125 NEW.logdate < DATE '2007-02-01' ) THEN
2126 INSERT INTO measurement_y2007m01 (city_id, logdate, peaktemp, unitsales)
2127 VALUES (NEW.*);
2128 ELSE
2129 RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!';
2130 END IF;
2131 RETURN NULL;
2132 END;
2133 $$ LANGUAGE plpgsql ;
2134 CREATE TRIGGER insert_measurement_trigger
2135 BEFORE INSERT ON measurement
2136 FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();
2137 INSERT INTO measurement VALUES (1, '2006-02-10', 35, 10);
2138 INSERT INTO measurement VALUES (1, '2006-02-16', 45, 20);
2139 INSERT INTO measurement VALUES (1, '2006-03-17', 25, 10);
2140 INSERT INTO measurement VALUES (1, '2006-03-27', 15, 40);
2141 INSERT INTO measurement VALUES (1, '2007-01-15', 10, 10);
2142 INSERT INTO measurement VALUES (1, '2007-01-17', 10, 10);
2143 SELECT tableoid::regclass, * FROM measurement ORDER BY city_id, logdate;
2144 tableoid | city_id | logdate | peaktemp | unitsales
2145 ----------------------+---------+------------+----------+-----------
2146 measurement | 0 | 07-21-2005 | 5 | 15
2147 measurement_y2006m02 | 1 | 02-10-2006 | 35 | 10
2148 measurement_y2006m02 | 1 | 02-16-2006 | 45 | 20
2149 measurement_y2006m03 | 1 | 03-17-2006 | 25 | 10
2150 measurement_y2006m03 | 1 | 03-27-2006 | 15 | 40
2151 measurement_y2007m01 | 1 | 01-15-2007 | 10 | 10
2152 measurement_y2007m01 | 1 | 01-17-2007 | 10 | 10
2153 (7 rows)
2155 CREATE TABLE new_measurement (LIKE measurement) WITH (autovacuum_enabled=off);
2156 INSERT INTO new_measurement VALUES (0, '2005-07-21', 25, 20);
2157 INSERT INTO new_measurement VALUES (1, '2006-03-01', 20, 10);
2158 INSERT INTO new_measurement VALUES (1, '2006-02-16', 50, 10);
2159 INSERT INTO new_measurement VALUES (2, '2006-02-10', 20, 20);
2160 INSERT INTO new_measurement VALUES (1, '2006-03-27', NULL, NULL);
2161 INSERT INTO new_measurement VALUES (1, '2007-01-17', NULL, NULL);
2162 INSERT INTO new_measurement VALUES (1, '2007-01-15', 5, NULL);
2163 INSERT INTO new_measurement VALUES (1, '2007-01-16', 10, 10);
2164 BEGIN;
2165 MERGE INTO ONLY measurement m
2166 USING new_measurement nm ON
2167 (m.city_id = nm.city_id and m.logdate=nm.logdate)
2168 WHEN MATCHED AND nm.peaktemp IS NULL THEN DELETE
2169 WHEN MATCHED THEN UPDATE
2170 SET peaktemp = greatest(m.peaktemp, nm.peaktemp),
2171 unitsales = m.unitsales + coalesce(nm.unitsales, 0)
2172 WHEN NOT MATCHED THEN INSERT
2173 (city_id, logdate, peaktemp, unitsales)
2174 VALUES (city_id, logdate, peaktemp, unitsales);
2175 SELECT tableoid::regclass, * FROM measurement ORDER BY city_id, logdate, peaktemp;
2176 tableoid | city_id | logdate | peaktemp | unitsales
2177 ----------------------+---------+------------+----------+-----------
2178 measurement | 0 | 07-21-2005 | 25 | 35
2179 measurement_y2006m02 | 1 | 02-10-2006 | 35 | 10
2180 measurement_y2006m02 | 1 | 02-16-2006 | 45 | 20
2181 measurement_y2006m02 | 1 | 02-16-2006 | 50 | 10
2182 measurement_y2006m03 | 1 | 03-01-2006 | 20 | 10
2183 measurement_y2006m03 | 1 | 03-17-2006 | 25 | 10
2184 measurement_y2006m03 | 1 | 03-27-2006 | 15 | 40
2185 measurement_y2006m03 | 1 | 03-27-2006 | |
2186 measurement_y2007m01 | 1 | 01-15-2007 | 5 |
2187 measurement_y2007m01 | 1 | 01-15-2007 | 10 | 10
2188 measurement_y2007m01 | 1 | 01-16-2007 | 10 | 10
2189 measurement_y2007m01 | 1 | 01-17-2007 | 10 | 10
2190 measurement_y2007m01 | 1 | 01-17-2007 | |
2191 measurement_y2006m02 | 2 | 02-10-2006 | 20 | 20
2192 (14 rows)
2194 ROLLBACK;
2195 MERGE into measurement m
2196 USING new_measurement nm ON
2197 (m.city_id = nm.city_id and m.logdate=nm.logdate)
2198 WHEN MATCHED AND nm.peaktemp IS NULL THEN DELETE
2199 WHEN MATCHED THEN UPDATE
2200 SET peaktemp = greatest(m.peaktemp, nm.peaktemp),
2201 unitsales = m.unitsales + coalesce(nm.unitsales, 0)
2202 WHEN NOT MATCHED THEN INSERT
2203 (city_id, logdate, peaktemp, unitsales)
2204 VALUES (city_id, logdate, peaktemp, unitsales);
2205 SELECT tableoid::regclass, * FROM measurement ORDER BY city_id, logdate;
2206 tableoid | city_id | logdate | peaktemp | unitsales
2207 ----------------------+---------+------------+----------+-----------
2208 measurement | 0 | 07-21-2005 | 25 | 35
2209 measurement_y2006m02 | 1 | 02-10-2006 | 35 | 10
2210 measurement_y2006m02 | 1 | 02-16-2006 | 50 | 30
2211 measurement_y2006m03 | 1 | 03-01-2006 | 20 | 10
2212 measurement_y2006m03 | 1 | 03-17-2006 | 25 | 10
2213 measurement_y2007m01 | 1 | 01-15-2007 | 10 | 10
2214 measurement_y2007m01 | 1 | 01-16-2007 | 10 | 10
2215 measurement_y2006m02 | 2 | 02-10-2006 | 20 | 20
2216 (8 rows)
2218 BEGIN;
2219 MERGE INTO new_measurement nm
2220 USING ONLY measurement m ON
2221 (nm.city_id = m.city_id and nm.logdate=m.logdate)
2222 WHEN MATCHED THEN DELETE;
2223 SELECT * FROM new_measurement ORDER BY city_id, logdate;
2224 city_id | logdate | peaktemp | unitsales
2225 ---------+------------+----------+-----------
2226 1 | 02-16-2006 | 50 | 10
2227 1 | 03-01-2006 | 20 | 10
2228 1 | 03-27-2006 | |
2229 1 | 01-15-2007 | 5 |
2230 1 | 01-16-2007 | 10 | 10
2231 1 | 01-17-2007 | |
2232 2 | 02-10-2006 | 20 | 20
2233 (7 rows)
2235 ROLLBACK;
2236 MERGE INTO new_measurement nm
2237 USING measurement m ON
2238 (nm.city_id = m.city_id and nm.logdate=m.logdate)
2239 WHEN MATCHED THEN DELETE;
2240 SELECT * FROM new_measurement ORDER BY city_id, logdate;
2241 city_id | logdate | peaktemp | unitsales
2242 ---------+------------+----------+-----------
2243 1 | 03-27-2006 | |
2244 1 | 01-17-2007 | |
2245 (2 rows)
2247 DROP TABLE measurement, new_measurement CASCADE;
2248 NOTICE: drop cascades to 3 other objects
2249 DETAIL: drop cascades to table measurement_y2006m02
2250 drop cascades to table measurement_y2006m03
2251 drop cascades to table measurement_y2007m01
2252 DROP FUNCTION measurement_insert_trigger();
2253 -- prepare
2254 RESET SESSION AUTHORIZATION;
2255 DROP TABLE target, target2;
2256 DROP TABLE source, source2;
2257 DROP FUNCTION merge_trigfunc();
2258 DROP USER regress_merge_privs;
2259 DROP USER regress_merge_no_privs;
2260 DROP USER regress_merge_none;