src/test/regress/sql/merge.sql

   1 --
   2 -- MERGE
   3 --
   4
   5 CREATE USER regress_merge_privs;
   6 CREATE USER regress_merge_no_privs;
   7 CREATE USER regress_merge_none;
   8
   9 DROP TABLE IF EXISTS target;
  10 DROP TABLE IF EXISTS source;
  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
  20 ALTER TABLE target OWNER TO regress_merge_privs;
  21 ALTER TABLE source OWNER TO regress_merge_privs;
  22
  23 CREATE TABLE target2 (tid integer, balance integer)
  24   WITH (autovacuum_enabled=off);
  25 CREATE TABLE source2 (sid integer, delta integer)
  26   WITH (autovacuum_enabled=off);
  27
  28 ALTER TABLE target2 OWNER TO regress_merge_no_privs;
  29 ALTER TABLE source2 OWNER TO regress_merge_no_privs;
  30
  31 GRANT INSERT ON target TO regress_merge_no_privs;
  32
  33 SET SESSION AUTHORIZATION regress_merge_privs;
  34
  35 EXPLAIN (COSTS OFF)
  36 MERGE INTO target t
  37 USING source AS s
  38 ON t.tid = s.sid
  39 WHEN MATCHED THEN
  40         DELETE;
  41
  42 --
  43 -- Errors
  44 --
  45 MERGE INTO target t RANDOMWORD
  46 USING source AS s
  47 ON t.tid = s.sid
  48 WHEN MATCHED THEN
  49         UPDATE SET balance = 0;
  50 -- MATCHED/INSERT error
  51 MERGE INTO target t
  52 USING source AS s
  53 ON t.tid = s.sid
  54 WHEN MATCHED THEN
  55         INSERT DEFAULT VALUES;
  56 -- incorrectly specifying INTO target
  57 MERGE INTO target t
  58 USING source AS s
  59 ON t.tid = s.sid
  60 WHEN NOT MATCHED THEN
  61         INSERT INTO target DEFAULT VALUES;
  62 -- Multiple VALUES clause
  63 MERGE INTO target t
  64 USING source AS s
  65 ON t.tid = s.sid
  66 WHEN NOT MATCHED THEN
  67         INSERT VALUES (1,1), (2,2);
  68 -- SELECT query for INSERT
  69 MERGE INTO target t
  70 USING source AS s
  71 ON t.tid = s.sid
  72 WHEN NOT MATCHED THEN
  73         INSERT SELECT (1, 1);
  74 -- NOT MATCHED/UPDATE
  75 MERGE INTO target t
  76 USING source AS s
  77 ON t.tid = s.sid
  78 WHEN NOT MATCHED THEN
  79         UPDATE SET balance = 0;
  80 -- UPDATE tablename
  81 MERGE INTO target t
  82 USING source AS s
  83 ON t.tid = s.sid
  84 WHEN MATCHED THEN
  85         UPDATE target SET balance = 0;
  86 -- source and target names the same
  87 MERGE INTO target
  88 USING target
  89 ON tid = tid
  90 WHEN MATCHED THEN DO NOTHING;
  91 -- used in a CTE
  92 WITH foo AS (
  93   MERGE INTO target USING source ON (true)
  94   WHEN MATCHED THEN DELETE
  95 ) SELECT * FROM foo;
  96 -- used in COPY
  97 COPY (
  98   MERGE INTO target USING source ON (true)
  99   WHEN MATCHED THEN DELETE
 100 ) TO stdout;
 101
 102 -- unsupported relation types
 103 -- view
 104 CREATE VIEW tv AS SELECT * FROM target;
 105 MERGE INTO tv t
 106 USING source s
 107 ON t.tid = s.sid
 108 WHEN NOT MATCHED THEN
 109         INSERT DEFAULT VALUES;
 110 DROP VIEW tv;
 111
 112 -- materialized view
 113 CREATE MATERIALIZED VIEW mv AS SELECT * FROM target;
 114 MERGE INTO mv t
 115 USING source s
 116 ON t.tid = s.sid
 117 WHEN NOT MATCHED THEN
 118         INSERT DEFAULT VALUES;
 119 DROP MATERIALIZED VIEW mv;
 120
 121 -- permissions
 122
 123 SET SESSION AUTHORIZATION regress_merge_none;
 124 MERGE INTO target
 125 USING (SELECT 1)
 126 ON true
 127 WHEN MATCHED THEN
 128         DO NOTHING;
 129
 130 SET SESSION AUTHORIZATION regress_merge_privs;
 131 MERGE INTO target
 132 USING source2
 133 ON target.tid = source2.sid
 134 WHEN MATCHED THEN
 135         UPDATE SET balance = 0;
 136
 137 GRANT INSERT ON target TO regress_merge_no_privs;
 138 SET SESSION AUTHORIZATION regress_merge_no_privs;
 139
 140 MERGE INTO target
 141 USING source2
 142 ON target.tid = source2.sid
 143 WHEN MATCHED THEN
 144         UPDATE SET balance = 0;
 145
 146 GRANT UPDATE ON target2 TO regress_merge_privs;
 147 SET SESSION AUTHORIZATION regress_merge_privs;
 148
 149 MERGE INTO target2
 150 USING source
 151 ON target2.tid = source.sid
 152 WHEN MATCHED THEN
 153         DELETE;
 154
 155 MERGE INTO target2
 156 USING source
 157 ON target2.tid = source.sid
 158 WHEN NOT MATCHED THEN
 159         INSERT DEFAULT VALUES;
 160
 161 -- check if the target can be accessed from source relation subquery; we should
 162 -- not be able to do so
 163 MERGE INTO target t
 164 USING (SELECT * FROM source WHERE t.tid > sid) s
 165 ON t.tid = s.sid
 166 WHEN NOT MATCHED THEN
 167         INSERT DEFAULT VALUES;
 168
 169 --
 170 -- initial tests
 171 --
 172 -- zero rows in source has no effect
 173 MERGE INTO target
 174 USING source
 175 ON target.tid = source.sid
 176 WHEN MATCHED THEN
 177         UPDATE SET balance = 0;
 178
 179 MERGE INTO target t
 180 USING source AS s
 181 ON t.tid = s.sid
 182 WHEN MATCHED THEN
 183         UPDATE SET balance = 0;
 184 MERGE INTO target t
 185 USING source AS s
 186 ON t.tid = s.sid
 187 WHEN MATCHED THEN
 188         DELETE;
 189 BEGIN;
 190 MERGE INTO target t
 191 USING source AS s
 192 ON t.tid = s.sid
 193 WHEN NOT MATCHED THEN
 194         INSERT DEFAULT VALUES;
 195 ROLLBACK;
 196
 197 -- insert some non-matching source rows to work from
 198 INSERT INTO source VALUES (4, 40);
 199 SELECT * FROM source ORDER BY sid;
 200 SELECT * FROM target ORDER BY tid;
 201
 202 MERGE INTO target t
 203 USING source AS s
 204 ON t.tid = s.sid
 205 WHEN NOT MATCHED THEN
 206         DO NOTHING;
 207 MERGE INTO target t
 208 USING source AS s
 209 ON t.tid = s.sid
 210 WHEN MATCHED THEN
 211         UPDATE SET balance = 0;
 212 MERGE INTO target t
 213 USING source AS s
 214 ON t.tid = s.sid
 215 WHEN MATCHED THEN
 216         DELETE;
 217 BEGIN;
 218 MERGE INTO target t
 219 USING source AS s
 220 ON t.tid = s.sid
 221 WHEN NOT MATCHED THEN
 222         INSERT DEFAULT VALUES;
 223 SELECT * FROM target ORDER BY tid;
 224 ROLLBACK;
 225
 226 -- index plans
 227 INSERT INTO target SELECT generate_series(1000,2500), 0;
 228 ALTER TABLE target ADD PRIMARY KEY (tid);
 229 ANALYZE target;
 230
 231 EXPLAIN (COSTS OFF)
 232 MERGE INTO target t
 233 USING source AS s
 234 ON t.tid = s.sid
 235 WHEN MATCHED THEN
 236         UPDATE SET balance = 0;
 237 EXPLAIN (COSTS OFF)
 238 MERGE INTO target t
 239 USING source AS s
 240 ON t.tid = s.sid
 241 WHEN MATCHED THEN
 242         DELETE;
 243 EXPLAIN (COSTS OFF)
 244 MERGE INTO target t
 245 USING source AS s
 246 ON t.tid = s.sid
 247 WHEN NOT MATCHED THEN
 248         INSERT VALUES (4, NULL);
 249 DELETE FROM target WHERE tid > 100;
 250 ANALYZE target;
 251
 252 -- insert some matching source rows to work from
 253 INSERT INTO source VALUES (2, 5);
 254 INSERT INTO source VALUES (3, 20);
 255 SELECT * FROM source ORDER BY sid;
 256 SELECT * FROM target ORDER BY tid;
 257
 258 -- equivalent of an UPDATE join
 259 BEGIN;
 260 MERGE INTO target t
 261 USING source AS s
 262 ON t.tid = s.sid
 263 WHEN MATCHED THEN
 264         UPDATE SET balance = 0;
 265 SELECT * FROM target ORDER BY tid;
 266 ROLLBACK;
 267
 268 -- equivalent of a DELETE join
 269 BEGIN;
 270 MERGE INTO target t
 271 USING source AS s
 272 ON t.tid = s.sid
 273 WHEN MATCHED THEN
 274         DELETE;
 275 SELECT * FROM target ORDER BY tid;
 276 ROLLBACK;
 277
 278 BEGIN;
 279 MERGE INTO target t
 280 USING source AS s
 281 ON t.tid = s.sid
 282 WHEN MATCHED THEN
 283         DO NOTHING;
 284 SELECT * FROM target ORDER BY tid;
 285 ROLLBACK;
 286
 287 BEGIN;
 288 MERGE INTO target t
 289 USING source AS s
 290 ON t.tid = s.sid
 291 WHEN NOT MATCHED THEN
 292         INSERT VALUES (4, NULL);
 293 SELECT * FROM target ORDER BY tid;
 294 ROLLBACK;
 295
 296 -- duplicate source row causes multiple target row update ERROR
 297 INSERT INTO source VALUES (2, 5);
 298 SELECT * FROM source ORDER BY sid;
 299 SELECT * FROM target ORDER BY tid;
 300 BEGIN;
 301 MERGE INTO target t
 302 USING source AS s
 303 ON t.tid = s.sid
 304 WHEN MATCHED THEN
 305         UPDATE SET balance = 0;
 306 ROLLBACK;
 307
 308 BEGIN;
 309 MERGE INTO target t
 310 USING source AS s
 311 ON t.tid = s.sid
 312 WHEN MATCHED THEN
 313         DELETE;
 314 ROLLBACK;
 315
 316 -- remove duplicate MATCHED data from source data
 317 DELETE FROM source WHERE sid = 2;
 318 INSERT INTO source VALUES (2, 5);
 319 SELECT * FROM source ORDER BY sid;
 320 SELECT * FROM target ORDER BY tid;
 321
 322 -- duplicate source row on INSERT should fail because of target_pkey
 323 INSERT INTO source VALUES (4, 40);
 324 BEGIN;
 325 MERGE INTO target t
 326 USING source AS s
 327 ON t.tid = s.sid
 328 WHEN NOT MATCHED THEN
 329   INSERT VALUES (4, NULL);
 330 SELECT * FROM target ORDER BY tid;
 331 ROLLBACK;
 332
 333 -- remove duplicate NOT MATCHED data from source data
 334 DELETE FROM source WHERE sid = 4;
 335 INSERT INTO source VALUES (4, 40);
 336 SELECT * FROM source ORDER BY sid;
 337 SELECT * FROM target ORDER BY tid;
 338
 339 -- remove constraints
 340 alter table target drop CONSTRAINT target_pkey;
 341 alter table target alter column tid drop not null;
 342
 343 -- multiple actions
 344 BEGIN;
 345 MERGE INTO target t
 346 USING source AS s
 347 ON t.tid = s.sid
 348 WHEN NOT MATCHED THEN
 349         INSERT VALUES (4, 4)
 350 WHEN MATCHED THEN
 351         UPDATE SET balance = 0;
 352 SELECT * FROM target ORDER BY tid;
 353 ROLLBACK;
 354
 355 -- should be equivalent
 356 BEGIN;
 357 MERGE INTO target t
 358 USING source AS s
 359 ON t.tid = s.sid
 360 WHEN MATCHED THEN
 361         UPDATE SET balance = 0
 362 WHEN NOT MATCHED THEN
 363         INSERT VALUES (4, 4);
 364 SELECT * FROM target ORDER BY tid;
 365 ROLLBACK;
 366
 367 -- column references
 368 -- do a simple equivalent of an UPDATE join
 369 BEGIN;
 370 MERGE INTO target t
 371 USING source AS s
 372 ON t.tid = s.sid
 373 WHEN MATCHED THEN
 374         UPDATE SET balance = t.balance + s.delta;
 375 SELECT * FROM target ORDER BY tid;
 376 ROLLBACK;
 377
 378 -- do a simple equivalent of an INSERT SELECT
 379 BEGIN;
 380 MERGE INTO target t
 381 USING source AS s
 382 ON t.tid = s.sid
 383 WHEN NOT MATCHED THEN
 384         INSERT VALUES (s.sid, s.delta);
 385 SELECT * FROM target ORDER BY tid;
 386 ROLLBACK;
 387
 388 -- and again with duplicate source rows
 389 INSERT INTO source VALUES (5, 50);
 390 INSERT INTO source VALUES (5, 50);
 391
 392 -- do a simple equivalent of an INSERT SELECT
 393 BEGIN;
 394 MERGE INTO target t
 395 USING source AS s
 396 ON t.tid = s.sid
 397 WHEN NOT MATCHED THEN
 398   INSERT VALUES (s.sid, s.delta);
 399 SELECT * FROM target ORDER BY tid;
 400 ROLLBACK;
 401
 402 -- removing duplicate source rows
 403 DELETE FROM source WHERE sid = 5;
 404
 405 -- and again with explicitly identified column list
 406 BEGIN;
 407 MERGE INTO target t
 408 USING source AS s
 409 ON t.tid = s.sid
 410 WHEN NOT MATCHED THEN
 411         INSERT (tid, balance) VALUES (s.sid, s.delta);
 412 SELECT * FROM target ORDER BY tid;
 413 ROLLBACK;
 414
 415 -- and again with a subtle error: referring to non-existent target row for NOT MATCHED
 416 MERGE INTO target t
 417 USING source AS s
 418 ON t.tid = s.sid
 419 WHEN NOT MATCHED THEN
 420         INSERT (tid, balance) VALUES (t.tid, s.delta);
 421
 422 -- and again with a constant ON clause
 423 BEGIN;
 424 MERGE INTO target t
 425 USING source AS s
 426 ON (SELECT true)
 427 WHEN NOT MATCHED THEN
 428         INSERT (tid, balance) VALUES (t.tid, s.delta);
 429 SELECT * FROM target ORDER BY tid;
 430 ROLLBACK;
 431
 432 -- now the classic UPSERT
 433 BEGIN;
 434 MERGE INTO target t
 435 USING source AS s
 436 ON t.tid = s.sid
 437 WHEN MATCHED THEN
 438         UPDATE SET balance = t.balance + s.delta
 439 WHEN NOT MATCHED THEN
 440         INSERT VALUES (s.sid, s.delta);
 441 SELECT * FROM target ORDER BY tid;
 442 ROLLBACK;
 443
 444 -- unreachable WHEN clause should ERROR
 445 BEGIN;
 446 MERGE INTO target t
 447 USING source AS s
 448 ON t.tid = s.sid
 449 WHEN MATCHED THEN /* Terminal WHEN clause for MATCHED */
 450         DELETE
 451 WHEN MATCHED THEN
 452         UPDATE SET balance = t.balance - s.delta;
 453 ROLLBACK;
 454
 455 -- conditional WHEN clause
 456 CREATE TABLE wq_target (tid integer not null, balance integer DEFAULT -1)
 457   WITH (autovacuum_enabled=off);
 458 CREATE TABLE wq_source (balance integer, sid integer)
 459   WITH (autovacuum_enabled=off);
 460
 461 INSERT INTO wq_source (sid, balance) VALUES (1, 100);
 462
 463 BEGIN;
 464 -- try a simple INSERT with default values first
 465 MERGE INTO wq_target t
 466 USING wq_source s ON t.tid = s.sid
 467 WHEN NOT MATCHED THEN
 468         INSERT (tid) VALUES (s.sid);
 469 SELECT * FROM wq_target;
 470 ROLLBACK;
 471
 472 -- this time with a FALSE condition
 473 MERGE INTO wq_target t
 474 USING wq_source s ON t.tid = s.sid
 475 WHEN NOT MATCHED AND FALSE THEN
 476         INSERT (tid) VALUES (s.sid);
 477 SELECT * FROM wq_target;
 478
 479 -- this time with an actual condition which returns false
 480 MERGE INTO wq_target t
 481 USING wq_source s ON t.tid = s.sid
 482 WHEN NOT MATCHED AND s.balance <> 100 THEN
 483         INSERT (tid) VALUES (s.sid);
 484 SELECT * FROM wq_target;
 485
 486 BEGIN;
 487 -- and now with a condition which returns true
 488 MERGE INTO wq_target t
 489 USING wq_source s ON t.tid = s.sid
 490 WHEN NOT MATCHED AND s.balance = 100 THEN
 491         INSERT (tid) VALUES (s.sid);
 492 SELECT * FROM wq_target;
 493 ROLLBACK;
 494
 495 -- conditions in the NOT MATCHED clause can only refer to source columns
 496 BEGIN;
 497 MERGE INTO wq_target t
 498 USING wq_source s ON t.tid = s.sid
 499 WHEN NOT MATCHED AND t.balance = 100 THEN
 500         INSERT (tid) VALUES (s.sid);
 501 SELECT * FROM wq_target;
 502 ROLLBACK;
 503
 504 MERGE INTO wq_target t
 505 USING wq_source s ON t.tid = s.sid
 506 WHEN NOT MATCHED AND s.balance = 100 THEN
 507         INSERT (tid) VALUES (s.sid);
 508 SELECT * FROM wq_target;
 509
 510 -- conditions in MATCHED clause can refer to both source and target
 511 SELECT * FROM wq_source;
 512 MERGE INTO wq_target t
 513 USING wq_source s ON t.tid = s.sid
 514 WHEN MATCHED AND s.balance = 100 THEN
 515         UPDATE SET balance = t.balance + s.balance;
 516 SELECT * FROM wq_target;
 517
 518 MERGE INTO wq_target t
 519 USING wq_source s ON t.tid = s.sid
 520 WHEN MATCHED AND t.balance = 100 THEN
 521         UPDATE SET balance = t.balance + s.balance;
 522 SELECT * FROM wq_target;
 523
 524 -- check if AND works
 525 MERGE INTO wq_target t
 526 USING wq_source s ON t.tid = s.sid
 527 WHEN MATCHED AND t.balance = 99 AND s.balance > 100 THEN
 528         UPDATE SET balance = t.balance + s.balance;
 529 SELECT * FROM wq_target;
 530
 531 MERGE INTO wq_target t
 532 USING wq_source s ON t.tid = s.sid
 533 WHEN MATCHED AND t.balance = 99 AND s.balance = 100 THEN
 534         UPDATE SET balance = t.balance + s.balance;
 535 SELECT * FROM wq_target;
 536
 537 -- check if OR works
 538 MERGE INTO wq_target t
 539 USING wq_source s ON t.tid = s.sid
 540 WHEN MATCHED AND t.balance = 99 OR s.balance > 100 THEN
 541         UPDATE SET balance = t.balance + s.balance;
 542 SELECT * FROM wq_target;
 543
 544 MERGE INTO wq_target t
 545 USING wq_source s ON t.tid = s.sid
 546 WHEN MATCHED AND t.balance = 199 OR s.balance > 100 THEN
 547         UPDATE SET balance = t.balance + s.balance;
 548 SELECT * FROM wq_target;
 549
 550 -- check source-side whole-row references
 551 BEGIN;
 552 MERGE INTO wq_target t
 553 USING wq_source s ON (t.tid = s.sid)
 554 WHEN matched and t = s or t.tid = s.sid THEN
 555         UPDATE SET balance = t.balance + s.balance;
 556 SELECT * FROM wq_target;
 557 ROLLBACK;
 558
 559 -- check if subqueries work in the conditions?
 560 MERGE INTO wq_target t
 561 USING wq_source s ON t.tid = s.sid
 562 WHEN MATCHED AND t.balance > (SELECT max(balance) FROM target) THEN
 563         UPDATE SET balance = t.balance + s.balance;
 564
 565 -- check if we can access system columns in the conditions
 566 MERGE INTO wq_target t
 567 USING wq_source s ON t.tid = s.sid
 568 WHEN MATCHED AND t.xmin = t.xmax THEN
 569         UPDATE SET balance = t.balance + s.balance;
 570
 571 MERGE INTO wq_target t
 572 USING wq_source s ON t.tid = s.sid
 573 WHEN MATCHED AND t.tableoid >= 0 THEN
 574         UPDATE SET balance = t.balance + s.balance;
 575 SELECT * FROM wq_target;
 576
 577 DROP TABLE wq_target, wq_source;
 578
 579 -- test triggers
 580 create or replace function merge_trigfunc () returns trigger
 581 language plpgsql as
 582 $$
 583 DECLARE
 584         line text;
 585 BEGIN
 586         SELECT INTO line format('%s %s %s trigger%s',
 587                 TG_WHEN, TG_OP, TG_LEVEL, CASE
 588                 WHEN TG_OP = 'INSERT' AND TG_LEVEL = 'ROW'
 589                         THEN format(' row: %s', NEW)
 590                 WHEN TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW'
 591                         THEN format(' row: %s -> %s', OLD, NEW)
 592                 WHEN TG_OP = 'DELETE' AND TG_LEVEL = 'ROW'
 593                         THEN format(' row: %s', OLD)
 594                 END);
 595
 596         RAISE NOTICE '%', line;
 597         IF (TG_WHEN = 'BEFORE' AND TG_LEVEL = 'ROW') THEN
 598                 IF (TG_OP = 'DELETE') THEN
 599                         RETURN OLD;
 600                 ELSE
 601                         RETURN NEW;
 602                 END IF;
 603         ELSE
 604                 RETURN NULL;
 605         END IF;
 606 END;
 607 $$;
 608 CREATE TRIGGER merge_bsi BEFORE INSERT ON target FOR EACH STATEMENT EXECUTE PROCEDURE merge_trigfunc ();
 609 CREATE TRIGGER merge_bsu BEFORE UPDATE ON target FOR EACH STATEMENT EXECUTE PROCEDURE merge_trigfunc ();
 610 CREATE TRIGGER merge_bsd BEFORE DELETE ON target FOR EACH STATEMENT EXECUTE PROCEDURE merge_trigfunc ();
 611 CREATE TRIGGER merge_asi AFTER INSERT ON target FOR EACH STATEMENT EXECUTE PROCEDURE merge_trigfunc ();
 612 CREATE TRIGGER merge_asu AFTER UPDATE ON target FOR EACH STATEMENT EXECUTE PROCEDURE merge_trigfunc ();
 613 CREATE TRIGGER merge_asd AFTER DELETE ON target FOR EACH STATEMENT EXECUTE PROCEDURE merge_trigfunc ();
 614 CREATE TRIGGER merge_bri BEFORE INSERT ON target FOR EACH ROW EXECUTE PROCEDURE merge_trigfunc ();
 615 CREATE TRIGGER merge_bru BEFORE UPDATE ON target FOR EACH ROW EXECUTE PROCEDURE merge_trigfunc ();
 616 CREATE TRIGGER merge_brd BEFORE DELETE ON target FOR EACH ROW EXECUTE PROCEDURE merge_trigfunc ();
 617 CREATE TRIGGER merge_ari AFTER INSERT ON target FOR EACH ROW EXECUTE PROCEDURE merge_trigfunc ();
 618 CREATE TRIGGER merge_aru AFTER UPDATE ON target FOR EACH ROW EXECUTE PROCEDURE merge_trigfunc ();
 619 CREATE TRIGGER merge_ard AFTER DELETE ON target FOR EACH ROW EXECUTE PROCEDURE merge_trigfunc ();
 620
 621 -- now the classic UPSERT, with a DELETE
 622 BEGIN;
 623 UPDATE target SET balance = 0 WHERE tid = 3;
 624 --EXPLAIN (ANALYZE ON, COSTS OFF, SUMMARY OFF, TIMING OFF)
 625 MERGE INTO target t
 626 USING source AS s
 627 ON t.tid = s.sid
 628 WHEN MATCHED AND t.balance > s.delta THEN
 629         UPDATE SET balance = t.balance - s.delta
 630 WHEN MATCHED THEN
 631         DELETE
 632 WHEN NOT MATCHED THEN
 633         INSERT VALUES (s.sid, s.delta);
 634 SELECT * FROM target ORDER BY tid;
 635 ROLLBACK;
 636
 637 -- Test behavior of triggers that turn UPDATE/DELETE into no-ops
 638 create or replace function skip_merge_op() returns trigger
 639 language plpgsql as
 640 $$
 641 BEGIN
 642         RETURN NULL;
 643 END;
 644 $$;
 645
 646 SELECT * FROM target full outer join source on (sid = tid);
 647 create trigger merge_skip BEFORE INSERT OR UPDATE or DELETE
 648   ON target FOR EACH ROW EXECUTE FUNCTION skip_merge_op();
 649 DO $$
 650 DECLARE
 651   result integer;
 652 BEGIN
 653 MERGE INTO target t
 654 USING source AS s
 655 ON t.tid = s.sid
 656 WHEN MATCHED AND s.sid = 3 THEN UPDATE SET balance = t.balance + s.delta
 657 WHEN MATCHED THEN DELETE
 658 WHEN NOT MATCHED THEN INSERT VALUES (sid, delta);
 659 IF FOUND THEN
 660   RAISE NOTICE 'Found';
 661 ELSE
 662   RAISE NOTICE 'Not found';
 663 END IF;
 664 GET DIAGNOSTICS result := ROW_COUNT;
 665 RAISE NOTICE 'ROW_COUNT = %', result;
 666 END;
 667 $$;
 668 SELECT * FROM target FULL OUTER JOIN source ON (sid = tid);
 669 DROP TRIGGER merge_skip ON target;
 670 DROP FUNCTION skip_merge_op();
 671
 672 -- test from PL/pgSQL
 673 -- make sure MERGE INTO isn't interpreted to mean returning variables like SELECT INTO
 674 BEGIN;
 675 DO LANGUAGE plpgsql $$
 676 BEGIN
 677 MERGE INTO target t
 678 USING source AS s
 679 ON t.tid = s.sid
 680 WHEN MATCHED AND t.balance > s.delta THEN
 681         UPDATE SET balance = t.balance - s.delta;
 682 END;
 683 $$;
 684 ROLLBACK;
 685
 686 --source constants
 687 BEGIN;
 688 MERGE INTO target t
 689 USING (SELECT 9 AS sid, 57 AS delta) AS s
 690 ON t.tid = s.sid
 691 WHEN NOT MATCHED THEN
 692         INSERT (tid, balance) VALUES (s.sid, s.delta);
 693 SELECT * FROM target ORDER BY tid;
 694 ROLLBACK;
 695
 696 --source query
 697 BEGIN;
 698 MERGE INTO target t
 699 USING (SELECT sid, delta FROM source WHERE delta > 0) AS s
 700 ON t.tid = s.sid
 701 WHEN NOT MATCHED THEN
 702         INSERT (tid, balance) VALUES (s.sid, s.delta);
 703 SELECT * FROM target ORDER BY tid;
 704 ROLLBACK;
 705
 706 BEGIN;
 707 MERGE INTO target t
 708 USING (SELECT sid, delta as newname FROM source WHERE delta > 0) AS s
 709 ON t.tid = s.sid
 710 WHEN NOT MATCHED THEN
 711         INSERT (tid, balance) VALUES (s.sid, s.newname);
 712 SELECT * FROM target ORDER BY tid;
 713 ROLLBACK;
 714
 715 --self-merge
 716 BEGIN;
 717 MERGE INTO target t1
 718 USING target t2
 719 ON t1.tid = t2.tid
 720 WHEN MATCHED THEN
 721         UPDATE SET balance = t1.balance + t2.balance
 722 WHEN NOT MATCHED THEN
 723         INSERT VALUES (t2.tid, t2.balance);
 724 SELECT * FROM target ORDER BY tid;
 725 ROLLBACK;
 726
 727 BEGIN;
 728 MERGE INTO target t
 729 USING (SELECT tid as sid, balance as delta FROM target WHERE balance > 0) AS s
 730 ON t.tid = s.sid
 731 WHEN NOT MATCHED THEN
 732         INSERT (tid, balance) VALUES (s.sid, s.delta);
 733 SELECT * FROM target ORDER BY tid;
 734 ROLLBACK;
 735
 736 BEGIN;
 737 MERGE INTO target t
 738 USING
 739 (SELECT sid, max(delta) AS delta
 740  FROM source
 741  GROUP BY sid
 742  HAVING count(*) = 1
 743  ORDER BY sid ASC) AS s
 744 ON t.tid = s.sid
 745 WHEN NOT MATCHED THEN
 746         INSERT (tid, balance) VALUES (s.sid, s.delta);
 747 SELECT * FROM target ORDER BY tid;
 748 ROLLBACK;
 749
 750 -- plpgsql parameters and results
 751 BEGIN;
 752 CREATE FUNCTION merge_func (p_id integer, p_bal integer)
 753 RETURNS INTEGER
 754 LANGUAGE plpgsql
 755 AS $$
 756 DECLARE
 757  result integer;
 758 BEGIN
 759 MERGE INTO target t
 760 USING (SELECT p_id AS sid) AS s
 761 ON t.tid = s.sid
 762 WHEN MATCHED THEN
 763         UPDATE SET balance = t.balance - p_bal;
 764 IF FOUND THEN
 765         GET DIAGNOSTICS result := ROW_COUNT;
 766 END IF;
 767 RETURN result;
 768 END;
 769 $$;
 770 SELECT merge_func(3, 4);
 771 SELECT * FROM target ORDER BY tid;
 772 ROLLBACK;
 773
 774 -- PREPARE
 775 BEGIN;
 776 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;
 777 execute foom;
 778 SELECT * FROM target ORDER BY tid;
 779 ROLLBACK;
 780
 781 BEGIN;
 782 PREPARE foom2 (integer, integer) AS
 783 MERGE INTO target t
 784 USING (SELECT 1) s
 785 ON t.tid = $1
 786 WHEN MATCHED THEN
 787 UPDATE SET balance = $2;
 788 --EXPLAIN (ANALYZE ON, COSTS OFF, SUMMARY OFF, TIMING OFF)
 789 execute foom2 (1, 1);
 790 SELECT * FROM target ORDER BY tid;
 791 ROLLBACK;
 792
 793 -- subqueries in source relation
 794
 795 CREATE TABLE sq_target (tid integer NOT NULL, balance integer)
 796   WITH (autovacuum_enabled=off);
 797 CREATE TABLE sq_source (delta integer, sid integer, balance integer DEFAULT 0)
 798   WITH (autovacuum_enabled=off);
 799
 800 INSERT INTO sq_target(tid, balance) VALUES (1,100), (2,200), (3,300);
 801 INSERT INTO sq_source(sid, delta) VALUES (1,10), (2,20), (4,40);
 802
 803 BEGIN;
 804 MERGE INTO sq_target t
 805 USING (SELECT * FROM sq_source) s
 806 ON tid = sid
 807 WHEN MATCHED AND t.balance > delta THEN
 808         UPDATE SET balance = t.balance + delta;
 809 SELECT * FROM sq_target;
 810 ROLLBACK;
 811
 812 -- try a view
 813 CREATE VIEW v AS SELECT * FROM sq_source WHERE sid < 2;
 814
 815 BEGIN;
 816 MERGE INTO sq_target
 817 USING v
 818 ON tid = sid
 819 WHEN MATCHED THEN
 820     UPDATE SET balance = v.balance + delta;
 821 SELECT * FROM sq_target;
 822 ROLLBACK;
 823
 824 -- ambiguous reference to a column
 825 BEGIN;
 826 MERGE INTO sq_target
 827 USING v
 828 ON tid = sid
 829 WHEN MATCHED AND tid > 2 THEN
 830     UPDATE SET balance = balance + delta
 831 WHEN NOT MATCHED THEN
 832         INSERT (balance, tid) VALUES (balance + delta, sid)
 833 WHEN MATCHED AND tid < 2 THEN
 834         DELETE;
 835 ROLLBACK;
 836
 837 BEGIN;
 838 INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10);
 839 MERGE INTO sq_target t
 840 USING v
 841 ON tid = sid
 842 WHEN MATCHED AND tid > 2 THEN
 843     UPDATE SET balance = t.balance + delta
 844 WHEN NOT MATCHED THEN
 845         INSERT (balance, tid) VALUES (balance + delta, sid)
 846 WHEN MATCHED AND tid < 2 THEN
 847         DELETE;
 848 SELECT * FROM sq_target;
 849 ROLLBACK;
 850
 851 -- CTEs
 852 BEGIN;
 853 INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10);
 854 WITH targq AS (
 855         SELECT * FROM v
 856 )
 857 MERGE INTO sq_target t
 858 USING v
 859 ON tid = sid
 860 WHEN MATCHED AND tid > 2 THEN
 861     UPDATE SET balance = t.balance + delta
 862 WHEN NOT MATCHED THEN
 863         INSERT (balance, tid) VALUES (balance + delta, sid)
 864 WHEN MATCHED AND tid < 2 THEN
 865         DELETE;
 866 ROLLBACK;
 867
 868 -- RETURNING
 869 BEGIN;
 870 INSERT INTO sq_source (sid, balance, delta) VALUES (-1, -1, -10);
 871 MERGE INTO sq_target t
 872 USING v
 873 ON tid = sid
 874 WHEN MATCHED AND tid > 2 THEN
 875     UPDATE SET balance = t.balance + delta
 876 WHEN NOT MATCHED THEN
 877         INSERT (balance, tid) VALUES (balance + delta, sid)
 878 WHEN MATCHED AND tid < 2 THEN
 879         DELETE
 880 RETURNING *;
 881 ROLLBACK;
 882
 883 -- EXPLAIN
 884 CREATE TABLE ex_mtarget (a int, b int)
 885   WITH (autovacuum_enabled=off);
 886 CREATE TABLE ex_msource (a int, b int)
 887   WITH (autovacuum_enabled=off);
 888 INSERT INTO ex_mtarget SELECT i, i*10 FROM generate_series(1,100,2) i;
 889 INSERT INTO ex_msource SELECT i, i*10 FROM generate_series(1,100,1) i;
 890
 891 CREATE FUNCTION explain_merge(query text) RETURNS SETOF text
 892 LANGUAGE plpgsql AS
 893 $$
 894 DECLARE ln text;
 895 BEGIN
 896     FOR ln IN
 897         EXECUTE 'explain (analyze, timing off, summary off, costs off) ' ||
 898                   query
 899     LOOP
 900         ln := regexp_replace(ln, '(Memory( Usage)?|Buckets|Batches): \S*',  '\1: xxx', 'g');
 901         RETURN NEXT ln;
 902     END LOOP;
 903 END;
 904 $$;
 905
 906 -- only updates
 907 SELECT explain_merge('
 908 MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
 909 WHEN MATCHED THEN
 910         UPDATE SET b = t.b + 1');
 911
 912 -- only updates to selected tuples
 913 SELECT explain_merge('
 914 MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
 915 WHEN MATCHED AND t.a < 10 THEN
 916         UPDATE SET b = t.b + 1');
 917
 918 -- updates + deletes
 919 SELECT explain_merge('
 920 MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
 921 WHEN MATCHED AND t.a < 10 THEN
 922         UPDATE SET b = t.b + 1
 923 WHEN MATCHED AND t.a >= 10 AND t.a <= 20 THEN
 924         DELETE');
 925
 926 -- only inserts
 927 SELECT explain_merge('
 928 MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
 929 WHEN NOT MATCHED AND s.a < 10 THEN
 930         INSERT VALUES (a, b)');
 931
 932 -- all three
 933 SELECT explain_merge('
 934 MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a
 935 WHEN MATCHED AND t.a < 10 THEN
 936         UPDATE SET b = t.b + 1
 937 WHEN MATCHED AND t.a >= 30 AND t.a <= 40 THEN
 938         DELETE
 939 WHEN NOT MATCHED AND s.a < 20 THEN
 940         INSERT VALUES (a, b)');
 941
 942 -- nothing
 943 SELECT explain_merge('
 944 MERGE INTO ex_mtarget t USING ex_msource s ON t.a = s.a AND t.a < -1000
 945 WHEN MATCHED AND t.a < 10 THEN
 946         DO NOTHING');
 947
 948 DROP TABLE ex_msource, ex_mtarget;
 949 DROP FUNCTION explain_merge(text);
 950
 951 -- Subqueries
 952 BEGIN;
 953 MERGE INTO sq_target t
 954 USING v
 955 ON tid = sid
 956 WHEN MATCHED THEN
 957     UPDATE SET balance = (SELECT count(*) FROM sq_target);
 958 SELECT * FROM sq_target WHERE tid = 1;
 959 ROLLBACK;
 960
 961 BEGIN;
 962 MERGE INTO sq_target t
 963 USING v
 964 ON tid = sid
 965 WHEN MATCHED AND (SELECT count(*) > 0 FROM sq_target) THEN
 966     UPDATE SET balance = 42;
 967 SELECT * FROM sq_target WHERE tid = 1;
 968 ROLLBACK;
 969
 970 BEGIN;
 971 MERGE INTO sq_target t
 972 USING v
 973 ON tid = sid AND (SELECT count(*) > 0 FROM sq_target)
 974 WHEN MATCHED THEN
 975     UPDATE SET balance = 42;
 976 SELECT * FROM sq_target WHERE tid = 1;
 977 ROLLBACK;
 978
 979 DROP TABLE sq_target, sq_source CASCADE;
 980
 981 CREATE TABLE pa_target (tid integer, balance float, val text)
 982         PARTITION BY LIST (tid);
 983
 984 CREATE TABLE part1 PARTITION OF pa_target FOR VALUES IN (1,4)
 985   WITH (autovacuum_enabled=off);
 986 CREATE TABLE part2 PARTITION OF pa_target FOR VALUES IN (2,5,6)
 987   WITH (autovacuum_enabled=off);
 988 CREATE TABLE part3 PARTITION OF pa_target FOR VALUES IN (3,8,9)
 989   WITH (autovacuum_enabled=off);
 990 CREATE TABLE part4 PARTITION OF pa_target DEFAULT
 991   WITH (autovacuum_enabled=off);
 992
 993 CREATE TABLE pa_source (sid integer, delta float);
 994 -- insert many rows to the source table
 995 INSERT INTO pa_source SELECT id, id * 10  FROM generate_series(1,14) AS id;
 996 -- insert a few rows in the target table (odd numbered tid)
 997 INSERT INTO pa_target SELECT id, id * 100, 'initial' FROM generate_series(1,14,2) AS id;
 998
 999 -- try simple MERGE
1000 BEGIN;
1001 MERGE INTO pa_target t
1002   USING pa_source s
1003   ON t.tid = s.sid
1004   WHEN MATCHED THEN
1005     UPDATE SET balance = balance + delta, val = val || ' updated by merge'
1006   WHEN NOT MATCHED THEN
1007     INSERT VALUES (sid, delta, 'inserted by merge');
1008 SELECT * FROM pa_target ORDER BY tid;
1009 ROLLBACK;
1010
1011 -- same with a constant qual
1012 BEGIN;
1013 MERGE INTO pa_target t
1014   USING pa_source s
1015   ON t.tid = s.sid AND tid = 1
1016   WHEN MATCHED THEN
1017     UPDATE SET balance = balance + delta, val = val || ' updated by merge'
1018   WHEN NOT MATCHED THEN
1019     INSERT VALUES (sid, delta, 'inserted by merge');
1020 SELECT * FROM pa_target ORDER BY tid;
1021 ROLLBACK;
1022
1023 -- try updating the partition key column
1024 BEGIN;
1025 CREATE FUNCTION merge_func() RETURNS integer LANGUAGE plpgsql AS $$
1026 DECLARE
1027   result integer;
1028 BEGIN
1029 MERGE INTO pa_target t
1030   USING pa_source s
1031   ON t.tid = s.sid
1032   WHEN MATCHED THEN
1033     UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge'
1034   WHEN NOT MATCHED THEN
1035     INSERT VALUES (sid, delta, 'inserted by merge');
1036 IF FOUND THEN
1037   GET DIAGNOSTICS result := ROW_COUNT;
1038 END IF;
1039 RETURN result;
1040 END;
1041 $$;
1042 SELECT merge_func();
1043 SELECT * FROM pa_target ORDER BY tid;
1044 ROLLBACK;
1045
1046 DROP TABLE pa_target CASCADE;
1047
1048 -- The target table is partitioned in the same way, but this time by attaching
1049 -- partitions which have columns in different order, dropped columns etc.
1050 CREATE TABLE pa_target (tid integer, balance float, val text)
1051         PARTITION BY LIST (tid);
1052
1053 CREATE TABLE part1 (tid integer, balance float, val text)
1054   WITH (autovacuum_enabled=off);
1055 CREATE TABLE part2 (balance float, tid integer, val text)
1056   WITH (autovacuum_enabled=off);
1057 CREATE TABLE part3 (tid integer, balance float, val text)
1058   WITH (autovacuum_enabled=off);
1059 CREATE TABLE part4 (extraid text, tid integer, balance float, val text)
1060   WITH (autovacuum_enabled=off);
1061 ALTER TABLE part4 DROP COLUMN extraid;
1062
1063 ALTER TABLE pa_target ATTACH PARTITION part1 FOR VALUES IN (1,4);
1064 ALTER TABLE pa_target ATTACH PARTITION part2 FOR VALUES IN (2,5,6);
1065 ALTER TABLE pa_target ATTACH PARTITION part3 FOR VALUES IN (3,8,9);
1066 ALTER TABLE pa_target ATTACH PARTITION part4 DEFAULT;
1067
1068 -- insert a few rows in the target table (odd numbered tid)
1069 INSERT INTO pa_target SELECT id, id * 100, 'initial' FROM generate_series(1,14,2) AS id;
1070
1071 -- try simple MERGE
1072 BEGIN;
1073 DO $$
1074 DECLARE
1075   result integer;
1076 BEGIN
1077 MERGE INTO pa_target t
1078   USING pa_source s
1079   ON t.tid = s.sid
1080   WHEN MATCHED THEN
1081     UPDATE SET balance = balance + delta, val = val || ' updated by merge'
1082   WHEN NOT MATCHED THEN
1083     INSERT VALUES (sid, delta, 'inserted by merge');
1084 GET DIAGNOSTICS result := ROW_COUNT;
1085 RAISE NOTICE 'ROW_COUNT = %', result;
1086 END;
1087 $$;
1088 SELECT * FROM pa_target ORDER BY tid;
1089 ROLLBACK;
1090
1091 -- same with a constant qual
1092 BEGIN;
1093 MERGE INTO pa_target t
1094   USING pa_source s
1095   ON t.tid = s.sid AND tid IN (1, 5)
1096   WHEN MATCHED AND tid % 5 = 0 THEN DELETE
1097   WHEN MATCHED THEN
1098     UPDATE SET balance = balance + delta, val = val || ' updated by merge'
1099   WHEN NOT MATCHED THEN
1100     INSERT VALUES (sid, delta, 'inserted by merge');
1101 SELECT * FROM pa_target ORDER BY tid;
1102 ROLLBACK;
1103
1104 -- try updating the partition key column
1105 BEGIN;
1106 DO $$
1107 DECLARE
1108   result integer;
1109 BEGIN
1110 MERGE INTO pa_target t
1111   USING pa_source s
1112   ON t.tid = s.sid
1113   WHEN MATCHED THEN
1114     UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge'
1115   WHEN NOT MATCHED THEN
1116     INSERT VALUES (sid, delta, 'inserted by merge');
1117 GET DIAGNOSTICS result := ROW_COUNT;
1118 RAISE NOTICE 'ROW_COUNT = %', result;
1119 END;
1120 $$;
1121 SELECT * FROM pa_target ORDER BY tid;
1122 ROLLBACK;
1123
1124 -- as above, but blocked by BEFORE DELETE ROW trigger
1125 BEGIN;
1126 CREATE FUNCTION trig_fn() RETURNS trigger LANGUAGE plpgsql AS
1127   $$ BEGIN RETURN NULL; END; $$;
1128 CREATE TRIGGER del_trig BEFORE DELETE ON pa_target
1129   FOR EACH ROW EXECUTE PROCEDURE trig_fn();
1130 DO $$
1131 DECLARE
1132   result integer;
1133 BEGIN
1134 MERGE INTO pa_target t
1135   USING pa_source s
1136   ON t.tid = s.sid
1137   WHEN MATCHED THEN
1138     UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge'
1139   WHEN NOT MATCHED THEN
1140     INSERT VALUES (sid, delta, 'inserted by merge');
1141 GET DIAGNOSTICS result := ROW_COUNT;
1142 RAISE NOTICE 'ROW_COUNT = %', result;
1143 END;
1144 $$;
1145 SELECT * FROM pa_target ORDER BY tid;
1146 ROLLBACK;
1147
1148 -- as above, but blocked by BEFORE INSERT ROW trigger
1149 BEGIN;
1150 CREATE FUNCTION trig_fn() RETURNS trigger LANGUAGE plpgsql AS
1151   $$ BEGIN RETURN NULL; END; $$;
1152 CREATE TRIGGER ins_trig BEFORE INSERT ON pa_target
1153   FOR EACH ROW EXECUTE PROCEDURE trig_fn();
1154 DO $$
1155 DECLARE
1156   result integer;
1157 BEGIN
1158 MERGE INTO pa_target t
1159   USING pa_source s
1160   ON t.tid = s.sid
1161   WHEN MATCHED THEN
1162     UPDATE SET tid = tid + 1, balance = balance + delta, val = val || ' updated by merge'
1163   WHEN NOT MATCHED THEN
1164     INSERT VALUES (sid, delta, 'inserted by merge');
1165 GET DIAGNOSTICS result := ROW_COUNT;
1166 RAISE NOTICE 'ROW_COUNT = %', result;
1167 END;
1168 $$;
1169 SELECT * FROM pa_target ORDER BY tid;
1170 ROLLBACK;
1171
1172 -- test RLS enforcement
1173 BEGIN;
1174 ALTER TABLE pa_target ENABLE ROW LEVEL SECURITY;
1175 ALTER TABLE pa_target FORCE ROW LEVEL SECURITY;
1176 CREATE POLICY pa_target_pol ON pa_target USING (tid != 0);
1177 MERGE INTO pa_target t
1178   USING pa_source s
1179   ON t.tid = s.sid AND t.tid IN (1,2,3,4)
1180   WHEN MATCHED THEN
1181     UPDATE SET tid = tid - 1;
1182 ROLLBACK;
1183
1184 DROP TABLE pa_source;
1185 DROP TABLE pa_target CASCADE;
1186
1187 -- Sub-partitioning
1188 CREATE TABLE pa_target (logts timestamp, tid integer, balance float, val text)
1189         PARTITION BY RANGE (logts);
1190
1191 CREATE TABLE part_m01 PARTITION OF pa_target
1192         FOR VALUES FROM ('2017-01-01') TO ('2017-02-01')
1193         PARTITION BY LIST (tid);
1194 CREATE TABLE part_m01_odd PARTITION OF part_m01
1195         FOR VALUES IN (1,3,5,7,9) WITH (autovacuum_enabled=off);
1196 CREATE TABLE part_m01_even PARTITION OF part_m01
1197         FOR VALUES IN (2,4,6,8) WITH (autovacuum_enabled=off);
1198 CREATE TABLE part_m02 PARTITION OF pa_target
1199         FOR VALUES FROM ('2017-02-01') TO ('2017-03-01')
1200         PARTITION BY LIST (tid);
1201 CREATE TABLE part_m02_odd PARTITION OF part_m02
1202         FOR VALUES IN (1,3,5,7,9) WITH (autovacuum_enabled=off);
1203 CREATE TABLE part_m02_even PARTITION OF part_m02
1204         FOR VALUES IN (2,4,6,8) WITH (autovacuum_enabled=off);
1205
1206 CREATE TABLE pa_source (sid integer, delta float)
1207   WITH (autovacuum_enabled=off);
1208 -- insert many rows to the source table
1209 INSERT INTO pa_source SELECT id, id * 10  FROM generate_series(1,14) AS id;
1210 -- insert a few rows in the target table (odd numbered tid)
1211 INSERT INTO pa_target SELECT '2017-01-31', id, id * 100, 'initial' FROM generate_series(1,9,3) AS id;
1212 INSERT INTO pa_target SELECT '2017-02-28', id, id * 100, 'initial' FROM generate_series(2,9,3) AS id;
1213
1214 -- try simple MERGE
1215 BEGIN;
1216 MERGE INTO pa_target t
1217   USING (SELECT '2017-01-15' AS slogts, * FROM pa_source WHERE sid < 10) s
1218   ON t.tid = s.sid
1219   WHEN MATCHED THEN
1220     UPDATE SET balance = balance + delta, val = val || ' updated by merge'
1221   WHEN NOT MATCHED THEN
1222     INSERT VALUES (slogts::timestamp, sid, delta, 'inserted by merge');
1223 SELECT * FROM pa_target ORDER BY tid;
1224 ROLLBACK;
1225
1226 DROP TABLE pa_source;
1227 DROP TABLE pa_target CASCADE;
1228
1229 -- Partitioned table with primary key
1230
1231 CREATE TABLE pa_target (tid integer PRIMARY KEY) PARTITION BY LIST (tid);
1232 CREATE TABLE pa_targetp PARTITION OF pa_target DEFAULT;
1233 CREATE TABLE pa_source (sid integer);
1234
1235 INSERT INTO pa_source VALUES (1), (2);
1236
1237 EXPLAIN (VERBOSE, COSTS OFF)
1238 MERGE INTO pa_target t USING pa_source s ON t.tid = s.sid
1239   WHEN NOT MATCHED THEN INSERT VALUES (s.sid);
1240
1241 MERGE INTO pa_target t USING pa_source s ON t.tid = s.sid
1242   WHEN NOT MATCHED THEN INSERT VALUES (s.sid);
1243
1244 TABLE pa_target;
1245
1246 -- Partition-less partitioned table
1247 -- (the bug we are checking for appeared only if table had partitions before)
1248
1249 DROP TABLE pa_targetp;
1250
1251 EXPLAIN (VERBOSE, COSTS OFF)
1252 MERGE INTO pa_target t USING pa_source s ON t.tid = s.sid
1253   WHEN NOT MATCHED THEN INSERT VALUES (s.sid);
1254
1255 MERGE INTO pa_target t USING pa_source s ON t.tid = s.sid
1256   WHEN NOT MATCHED THEN INSERT VALUES (s.sid);
1257
1258 DROP TABLE pa_source;
1259 DROP TABLE pa_target CASCADE;
1260
1261 -- some complex joins on the source side
1262
1263 CREATE TABLE cj_target (tid integer, balance float, val text)
1264   WITH (autovacuum_enabled=off);
1265 CREATE TABLE cj_source1 (sid1 integer, scat integer, delta integer)
1266   WITH (autovacuum_enabled=off);
1267 CREATE TABLE cj_source2 (sid2 integer, sval text)
1268   WITH (autovacuum_enabled=off);
1269 INSERT INTO cj_source1 VALUES (1, 10, 100);
1270 INSERT INTO cj_source1 VALUES (1, 20, 200);
1271 INSERT INTO cj_source1 VALUES (2, 20, 300);
1272 INSERT INTO cj_source1 VALUES (3, 10, 400);
1273 INSERT INTO cj_source2 VALUES (1, 'initial source2');
1274 INSERT INTO cj_source2 VALUES (2, 'initial source2');
1275 INSERT INTO cj_source2 VALUES (3, 'initial source2');
1276
1277 -- source relation is an unaliased join
1278 MERGE INTO cj_target t
1279 USING cj_source1 s1
1280         INNER JOIN cj_source2 s2 ON sid1 = sid2
1281 ON t.tid = sid1
1282 WHEN NOT MATCHED THEN
1283         INSERT VALUES (sid1, delta, sval);
1284
1285 -- try accessing columns from either side of the source join
1286 MERGE INTO cj_target t
1287 USING cj_source2 s2
1288         INNER JOIN cj_source1 s1 ON sid1 = sid2 AND scat = 20
1289 ON t.tid = sid1
1290 WHEN NOT MATCHED THEN
1291         INSERT VALUES (sid2, delta, sval)
1292 WHEN MATCHED THEN
1293         DELETE;
1294
1295 -- some simple expressions in INSERT targetlist
1296 MERGE INTO cj_target t
1297 USING cj_source2 s2
1298         INNER JOIN cj_source1 s1 ON sid1 = sid2
1299 ON t.tid = sid1
1300 WHEN NOT MATCHED THEN
1301         INSERT VALUES (sid2, delta + scat, sval)
1302 WHEN MATCHED THEN
1303         UPDATE SET val = val || ' updated by merge';
1304
1305 MERGE INTO cj_target t
1306 USING cj_source2 s2
1307         INNER JOIN cj_source1 s1 ON sid1 = sid2 AND scat = 20
1308 ON t.tid = sid1
1309 WHEN MATCHED THEN
1310         UPDATE SET val = val || ' ' || delta::text;
1311
1312 SELECT * FROM cj_target;
1313
1314 -- try it with an outer join and PlaceHolderVar
1315 MERGE INTO cj_target t
1316 USING (SELECT *, 'join input'::text AS phv FROM cj_source1) fj
1317         FULL JOIN cj_source2 fj2 ON fj.scat = fj2.sid2 * 10
1318 ON t.tid = fj.scat
1319 WHEN NOT MATCHED THEN
1320         INSERT (tid, balance, val) VALUES (fj.scat, fj.delta, fj.phv);
1321
1322 SELECT * FROM cj_target;
1323
1324 ALTER TABLE cj_source1 RENAME COLUMN sid1 TO sid;
1325 ALTER TABLE cj_source2 RENAME COLUMN sid2 TO sid;
1326
1327 TRUNCATE cj_target;
1328
1329 MERGE INTO cj_target t
1330 USING cj_source1 s1
1331         INNER JOIN cj_source2 s2 ON s1.sid = s2.sid
1332 ON t.tid = s1.sid
1333 WHEN NOT MATCHED THEN
1334         INSERT VALUES (s2.sid, delta, sval);
1335
1336 DROP TABLE cj_source2, cj_source1, cj_target;
1337
1338 -- Function scans
1339 CREATE TABLE fs_target (a int, b int, c text)
1340   WITH (autovacuum_enabled=off);
1341 MERGE INTO fs_target t
1342 USING generate_series(1,100,1) AS id
1343 ON t.a = id
1344 WHEN MATCHED THEN
1345         UPDATE SET b = b + id
1346 WHEN NOT MATCHED THEN
1347         INSERT VALUES (id, -1);
1348
1349 MERGE INTO fs_target t
1350 USING generate_series(1,100,2) AS id
1351 ON t.a = id
1352 WHEN MATCHED THEN
1353         UPDATE SET b = b + id, c = 'updated '|| id.*::text
1354 WHEN NOT MATCHED THEN
1355         INSERT VALUES (id, -1, 'inserted ' || id.*::text);
1356
1357 SELECT count(*) FROM fs_target;
1358 DROP TABLE fs_target;
1359
1360 -- SERIALIZABLE test
1361 -- handled in isolation tests
1362
1363 -- Inheritance-based partitioning
1364 CREATE TABLE measurement (
1365     city_id         int not null,
1366     logdate         date not null,
1367     peaktemp        int,
1368     unitsales       int
1369 ) WITH (autovacuum_enabled=off);
1370 CREATE TABLE measurement_y2006m02 (
1371     CHECK ( logdate >= DATE '2006-02-01' AND logdate < DATE '2006-03-01' )
1372 ) INHERITS (measurement) WITH (autovacuum_enabled=off);
1373 CREATE TABLE measurement_y2006m03 (
1374     CHECK ( logdate >= DATE '2006-03-01' AND logdate < DATE '2006-04-01' )
1375 ) INHERITS (measurement) WITH (autovacuum_enabled=off);
1376 CREATE TABLE measurement_y2007m01 (
1377     filler          text,
1378     peaktemp        int,
1379     logdate         date not null,
1380     city_id         int not null,
1381     unitsales       int
1382     CHECK ( logdate >= DATE '2007-01-01' AND logdate < DATE '2007-02-01')
1383 ) WITH (autovacuum_enabled=off);
1384 ALTER TABLE measurement_y2007m01 DROP COLUMN filler;
1385 ALTER TABLE measurement_y2007m01 INHERIT measurement;
1386 INSERT INTO measurement VALUES (0, '2005-07-21', 5, 15);
1387
1388 CREATE OR REPLACE FUNCTION measurement_insert_trigger()
1389 RETURNS TRIGGER AS $$
1390 BEGIN
1391     IF ( NEW.logdate >= DATE '2006-02-01' AND
1392          NEW.logdate < DATE '2006-03-01' ) THEN
1393         INSERT INTO measurement_y2006m02 VALUES (NEW.*);
1394     ELSIF ( NEW.logdate >= DATE '2006-03-01' AND
1395             NEW.logdate < DATE '2006-04-01' ) THEN
1396         INSERT INTO measurement_y2006m03 VALUES (NEW.*);
1397     ELSIF ( NEW.logdate >= DATE '2007-01-01' AND
1398             NEW.logdate < DATE '2007-02-01' ) THEN
1399         INSERT INTO measurement_y2007m01 (city_id, logdate, peaktemp, unitsales)
1400             VALUES (NEW.*);
1401     ELSE
1402         RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
1403     END IF;
1404     RETURN NULL;
1405 END;
1406 $$ LANGUAGE plpgsql ;
1407 CREATE TRIGGER insert_measurement_trigger
1408     BEFORE INSERT ON measurement
1409     FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();
1410 INSERT INTO measurement VALUES (1, '2006-02-10', 35, 10);
1411 INSERT INTO measurement VALUES (1, '2006-02-16', 45, 20);
1412 INSERT INTO measurement VALUES (1, '2006-03-17', 25, 10);
1413 INSERT INTO measurement VALUES (1, '2006-03-27', 15, 40);
1414 INSERT INTO measurement VALUES (1, '2007-01-15', 10, 10);
1415 INSERT INTO measurement VALUES (1, '2007-01-17', 10, 10);
1416
1417 SELECT tableoid::regclass, * FROM measurement ORDER BY city_id, logdate;
1418
1419 CREATE TABLE new_measurement (LIKE measurement) WITH (autovacuum_enabled=off);
1420 INSERT INTO new_measurement VALUES (0, '2005-07-21', 25, 20);
1421 INSERT INTO new_measurement VALUES (1, '2006-03-01', 20, 10);
1422 INSERT INTO new_measurement VALUES (1, '2006-02-16', 50, 10);
1423 INSERT INTO new_measurement VALUES (2, '2006-02-10', 20, 20);
1424 INSERT INTO new_measurement VALUES (1, '2006-03-27', NULL, NULL);
1425 INSERT INTO new_measurement VALUES (1, '2007-01-17', NULL, NULL);
1426 INSERT INTO new_measurement VALUES (1, '2007-01-15', 5, NULL);
1427 INSERT INTO new_measurement VALUES (1, '2007-01-16', 10, 10);
1428
1429 BEGIN;
1430 MERGE INTO ONLY measurement m
1431  USING new_measurement nm ON
1432       (m.city_id = nm.city_id and m.logdate=nm.logdate)
1433 WHEN MATCHED AND nm.peaktemp IS NULL THEN DELETE
1434 WHEN MATCHED THEN UPDATE
1435      SET peaktemp = greatest(m.peaktemp, nm.peaktemp),
1436         unitsales = m.unitsales + coalesce(nm.unitsales, 0)
1437 WHEN NOT MATCHED THEN INSERT
1438      (city_id, logdate, peaktemp, unitsales)
1439    VALUES (city_id, logdate, peaktemp, unitsales);
1440
1441 SELECT tableoid::regclass, * FROM measurement ORDER BY city_id, logdate, peaktemp;
1442 ROLLBACK;
1443
1444 MERGE into measurement m
1445  USING new_measurement nm ON
1446       (m.city_id = nm.city_id and m.logdate=nm.logdate)
1447 WHEN MATCHED AND nm.peaktemp IS NULL THEN DELETE
1448 WHEN MATCHED THEN UPDATE
1449      SET peaktemp = greatest(m.peaktemp, nm.peaktemp),
1450         unitsales = m.unitsales + coalesce(nm.unitsales, 0)
1451 WHEN NOT MATCHED THEN INSERT
1452      (city_id, logdate, peaktemp, unitsales)
1453    VALUES (city_id, logdate, peaktemp, unitsales);
1454
1455 SELECT tableoid::regclass, * FROM measurement ORDER BY city_id, logdate;
1456
1457 BEGIN;
1458 MERGE INTO new_measurement nm
1459  USING ONLY measurement m ON
1460       (nm.city_id = m.city_id and nm.logdate=m.logdate)
1461 WHEN MATCHED THEN DELETE;
1462
1463 SELECT * FROM new_measurement ORDER BY city_id, logdate;
1464 ROLLBACK;
1465
1466 MERGE INTO new_measurement nm
1467  USING measurement m ON
1468       (nm.city_id = m.city_id and nm.logdate=m.logdate)
1469 WHEN MATCHED THEN DELETE;
1470
1471 SELECT * FROM new_measurement ORDER BY city_id, logdate;
1472
1473 DROP TABLE measurement, new_measurement CASCADE;
1474 DROP FUNCTION measurement_insert_trigger();
1475
1476 -- prepare
1477
1478 RESET SESSION AUTHORIZATION;
1479 DROP TABLE target, target2;
1480 DROP TABLE source, source2;
1481 DROP FUNCTION merge_trigfunc();
1482 DROP USER regress_merge_privs;
1483 DROP USER regress_merge_no_privs;
1484 DROP USER regress_merge_none;