github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/logictest/testdata/logic_test/upsert (about) 1 # LogicTest: !3node-tenant 2 subtest strict 3 4 statement ok 5 CREATE TABLE ex( 6 foo INT PRIMARY KEY, 7 bar INT UNIQUE, 8 baz INT 9 ) 10 11 statement count 1 12 INSERT INTO ex(foo,bar,baz) VALUES (1,1,1) 13 14 statement count 0 15 INSERT INTO ex(foo,bar,baz) VALUES (1,1,1) ON CONFLICT DO NOTHING 16 17 statement count 0 18 INSERT INTO ex(foo,bar,baz) VALUES (2,1,1) ON CONFLICT DO NOTHING 19 20 # Do not insert conflicting first and last rows. 21 statement count 2 22 INSERT INTO ex(foo,bar,baz) VALUES (1,2,1), (3,2,2), (6,6,2), (2,1,1) ON CONFLICT DO NOTHING 23 24 query III colnames 25 SELECT * from ex ORDER BY foo 26 ---- 27 foo bar baz 28 1 1 1 29 3 2 2 30 6 6 2 31 32 query III colnames 33 INSERT INTO ex(foo,bar,baz) VALUES (4,3,1), (5,2,1) ON CONFLICT DO NOTHING RETURNING * 34 ---- 35 foo bar baz 36 4 3 1 37 38 statement ok 39 CREATE TABLE ex2( 40 a INT PRIMARY KEY, 41 b INT UNIQUE, 42 c INT, 43 d INT, 44 e INT, 45 UNIQUE (c,d) 46 ) 47 48 statement count 1 49 INSERT INTO ex2(a,b,c,d,e) VALUES (0,0,0,0,0) 50 51 statement count 0 52 INSERT INTO ex2(a,b,c,d,e) VALUES (1,0,1,1,0), (2,4,0,0,5) ON CONFLICT DO NOTHING 53 54 statement count 3 55 INSERT INTO ex2(a,b,c,d,e) VALUES (3,4,5,6,7), (8,9,10,11,12), (13,14,15,16,17) ON CONFLICT DO NOTHING 56 57 statement count 0 58 INSERT INTO ex2(a,b,c,d,e) VALUES (3,4,5,6,7), (8,9,10,11,12) ON CONFLICT DO NOTHING 59 60 statement ok 61 CREATE TABLE no_unique( 62 a INT, 63 b INT 64 ) 65 66 statement count 1 67 INSERT INTO no_unique(a,b) VALUES (1,2) 68 69 statement count 1 70 INSERT INTO no_unique(a,b) VALUES (1,2) ON CONFLICT DO NOTHING 71 72 statement count 3 73 INSERT INTO no_unique(a,b) VALUES (1,2), (1,3), (3,2) ON CONFLICT DO NOTHING 74 75 query II colnames 76 SELECT * from no_unique ORDER BY a, b 77 ---- 78 a b 79 1 2 80 1 2 81 1 2 82 1 3 83 3 2 84 85 statement count 3 86 INSERT INTO no_unique(a,b) VALUES (1,2), (1,2), (1,2) ON CONFLICT DO NOTHING 87 88 subtest notstrict 89 90 statement ok 91 CREATE TABLE kv ( 92 k INT PRIMARY KEY, 93 v INT 94 ) 95 96 statement count 3 97 INSERT INTO kv VALUES (1, 1), (2, 2), (3, 3) ON CONFLICT (k) DO UPDATE SET v = excluded.v 98 99 query II 100 SELECT * FROM kv ORDER BY (k, v) 101 ---- 102 1 1 103 2 2 104 3 3 105 106 statement error multiple assignments to the same column 107 INSERT INTO kv VALUES (4, 4), (2, 5), (6, 6) ON CONFLICT (k) DO UPDATE SET v = 1, v = 1 108 109 statement count 3 110 INSERT INTO kv VALUES (4, 4), (2, 5), (6, 6) ON CONFLICT (k) DO UPDATE SET v = excluded.v 111 112 statement count 3 113 UPSERT INTO kv VALUES (7, 7), (3, 8), (9, 9) 114 115 statement count 1 116 INSERT INTO kv VALUES (1, 10) ON CONFLICT (k) DO UPDATE SET v = (SELECT CAST(sum(k) AS INT) FROM kv) 117 118 statement error column reference "v" is ambiguous \(candidates: excluded.v, kv.v\) 119 INSERT INTO kv VALUES (4, 10) ON CONFLICT (k) DO UPDATE SET v = v + 1 120 121 statement count 1 122 INSERT INTO kv VALUES (4, 10) ON CONFLICT (k) DO UPDATE SET v = kv.v + 20 123 124 statement error there is no unique or exclusion constraint matching the ON CONFLICT specification 125 INSERT INTO kv VALUES (4, 10) ON CONFLICT DO UPDATE SET v = kv.v + 20 126 127 statement error duplicate key value \(k\)=\(3\) violates unique constraint "primary" 128 INSERT INTO kv VALUES (2, 10) ON CONFLICT (k) DO UPDATE SET k = 3, v = 10 129 130 statement count 1 131 INSERT INTO kv VALUES (9, 9) ON CONFLICT (k) DO UPDATE SET (k, v) = (excluded.k + 2, excluded.v + 3) 132 133 statement count 1 134 UPSERT INTO kv VALUES (10, 10) 135 136 statement count 2 137 UPSERT INTO kv VALUES (10, 11), (10, 12) 138 139 query II rowsort 140 UPSERT INTO kv VALUES (11, 11), (10, 13) RETURNING k, v 141 ---- 142 11 11 143 10 13 144 145 query I 146 UPSERT INTO kv VALUES (11) RETURNING k 147 ---- 148 11 149 150 query I 151 UPSERT INTO kv VALUES (11, 12) RETURNING v 152 ---- 153 12 154 155 statement count 1 156 INSERT INTO kv VALUES (13, 13), (7, 8) ON CONFLICT (k) DO NOTHING RETURNING * 157 158 statement count 0 159 INSERT INTO kv VALUES (13, 13), (7, 8) ON CONFLICT DO NOTHING 160 161 statement count 2 162 INSERT INTO kv VALUES (14, 14), (13, 15) ON CONFLICT (k) DO UPDATE SET v = excluded.v + 1 163 164 statement count 2 165 INSERT INTO kv VALUES (15, 15), (14, 16) ON CONFLICT (k) DO UPDATE SET k = excluded.k * 10 166 167 statement count 2 168 INSERT INTO kv VALUES (16, 16), (15, 17) ON CONFLICT (k) DO UPDATE SET k = excluded.k * 10, v = excluded.v 169 170 query II 171 SELECT * FROM kv ORDER BY (k, v) 172 ---- 173 1 32 174 2 5 175 3 8 176 4 24 177 6 6 178 7 7 179 10 13 180 11 12 181 13 16 182 16 16 183 140 14 184 150 17 185 186 # TODO(knz): Enable the 1st statement and remove the 2nd once #33313 is fixed. 187 #query II rowsort 188 #UPSERT INTO kv(k) VALUES (6), (8) RETURNING k,v 189 #---- 190 #6 6 191 #8 NULL 192 query II rowsort 193 UPSERT INTO kv(k) VALUES (8) RETURNING k,v 194 ---- 195 8 NULL 196 197 query II rowsort 198 INSERT INTO kv VALUES (10, 10), (11, 11) ON CONFLICT (k) DO UPDATE SET v = excluded.v RETURNING * 199 ---- 200 10 10 201 11 11 202 203 query II rowsort 204 INSERT INTO kv VALUES (10, 2), (11, 3) ON CONFLICT (k) DO UPDATE SET v = excluded.v + kv.v RETURNING * 205 ---- 206 10 12 207 11 14 208 209 query II rowsort 210 INSERT INTO kv VALUES (10, 14), (15, 15) ON CONFLICT (k) DO NOTHING RETURNING * 211 ---- 212 15 15 213 214 statement ok 215 CREATE TABLE abc ( 216 a INT, 217 b INT, 218 c INT DEFAULT 7, 219 PRIMARY KEY (a, b), 220 INDEX y (b), 221 UNIQUE INDEX z (c) 222 ) 223 224 statement error missing "b" primary key column 225 UPSERT INTO abc (a, c) VALUES (1, 1) 226 227 statement error missing "a" primary key column 228 UPSERT INTO abc (b, c) VALUES (1, 1) 229 230 statement count 1 231 INSERT INTO abc VALUES (1, 2, 3) 232 233 statement count 1 234 INSERT INTO abc VALUES (1, 2, 3) ON CONFLICT (c) DO UPDATE SET a = 4 235 236 query III 237 SELECT * FROM abc 238 ---- 239 4 2 3 240 241 statement count 1 242 INSERT INTO abc VALUES (1, 2, 3) ON CONFLICT (c) DO UPDATE SET b = 5 243 244 statement count 1 245 INSERT INTO abc VALUES (1, 2, 3) ON CONFLICT (c) DO UPDATE SET c = 6 246 247 query III 248 SELECT * FROM abc 249 ---- 250 4 5 6 251 252 statement count 1 253 INSERT INTO abc (a, b) VALUES (1, 2) ON CONFLICT (a, b) DO UPDATE SET a = 1, b = 2 254 255 statement count 1 256 INSERT INTO abc (a, b) VALUES (4, 5) ON CONFLICT (a, b) DO UPDATE SET a = 7, b = 8 257 258 query III 259 SELECT * FROM abc ORDER BY (a, b, c) 260 ---- 261 1 2 7 262 7 8 6 263 264 statement count 1 265 DELETE FROM abc where a = 1 266 267 statement count 1 268 UPSERT INTO abc VALUES (1, 2) 269 270 query III 271 SELECT * FROM abc ORDER BY (a, b, c) 272 ---- 273 1 2 7 274 7 8 6 275 276 statement count 1 277 UPSERT INTO abc VALUES (1, 2, 5) 278 279 query III 280 SELECT * FROM abc ORDER BY (a, b, c) 281 ---- 282 1 2 5 283 7 8 6 284 285 statement count 1 286 UPSERT INTO abc VALUES (1, 2) 287 288 query III 289 SELECT * FROM abc ORDER BY (a, b, c) 290 ---- 291 1 2 7 292 7 8 6 293 294 statement count 1 295 DELETE FROM abc where a = 1 296 297 statement count 1 298 INSERT INTO abc VALUES (7, 8, 9) ON CONFLICT (a, b) DO UPDATE SET c = DEFAULT 299 300 query III 301 SELECT * FROM abc ORDER BY (a, b, c) 302 ---- 303 7 8 7 304 305 statement ok 306 CREATE TABLE excluded (a INT PRIMARY KEY, b INT) 307 308 statement error ambiguous source name: "excluded" 309 INSERT INTO excluded VALUES (1, 1) ON CONFLICT (a) DO UPDATE SET b = excluded.b 310 311 # Tests for upsert/on conflict returning 312 statement ok 313 CREATE TABLE upsert_returning (a INT PRIMARY KEY, b INT, c INT, d INT DEFAULT -1) 314 315 statement count 1 316 INSERT INTO upsert_returning VALUES (1, 1, NULL) 317 318 # Handle INSERT ... ON CONFLICT ... RETURNING 319 query IIII rowsort 320 INSERT INTO upsert_returning (a, c) VALUES (1, 1), (2, 2) ON CONFLICT (a) DO UPDATE SET c = excluded.c RETURNING * 321 ---- 322 1 1 1 -1 323 2 NULL 2 -1 324 325 # Handle INSERT ... ON CONFLICT DO NOTHING ... RETURNING 326 query IIII 327 INSERT INTO upsert_returning (a, c) VALUES (1, 1), (3, 3) ON CONFLICT (a) DO NOTHING RETURNING * 328 ---- 329 3 NULL 3 -1 330 331 # Handle UPSERT ... RETURNING 332 query IIII rowsort 333 UPSERT INTO upsert_returning (a, c) VALUES (1, 10), (3, 30) RETURNING * 334 ---- 335 1 1 10 -1 336 3 NULL 30 -1 337 338 # Ensure returned values are inserted values after conflict resolution 339 query I 340 SELECT b FROM upsert_returning WHERE a = 1 341 ---- 342 1 343 344 query I 345 INSERT INTO upsert_returning (a, b) VALUES (1, 1) ON CONFLICT (a) DO UPDATE SET b = excluded.b + upsert_returning.b + 1 RETURNING b 346 ---- 347 3 348 349 # Handle expressions within returning clause 350 query I rowsort 351 UPSERT INTO upsert_returning (a, b) VALUES (1, 2), (2, 3), (4, 3) RETURNING a+b+d 352 ---- 353 2 354 4 355 6 356 357 # Handle upsert fast path with autocommit 358 query IIII rowsort 359 UPSERT INTO upsert_returning VALUES (1, 2, 3, 4), (5, 6, 7, 8) RETURNING * 360 ---- 361 1 2 3 4 362 5 6 7 8 363 364 # Handle upsert fast path without autocommit 365 statement ok 366 BEGIN 367 368 query IIII rowsort 369 upsert INTO upsert_returning VALUES (1, 5, 4, 3), (6, 5, 4, 3) RETURNING * 370 ---- 371 1 5 4 3 372 6 5 4 3 373 374 statement ok 375 COMMIT 376 377 # For #22300. Test UPSERT ... RETURNING with UNION. 378 query I rowsort 379 SELECT a FROM [UPSERT INTO upsert_returning VALUES (7) RETURNING a] UNION VALUES (8) 380 ---- 381 7 382 8 383 384 # For #6710. Add an unused column to disable the fast path which doesn't have this bug. 385 statement ok 386 CREATE TABLE issue_6710 (a INT PRIMARY KEY, b STRING, c INT) 387 388 statement count 2 389 INSERT INTO issue_6710 (a, b) VALUES (1, 'foo'), (2, 'bar') 390 391 statement count 2 392 UPSERT INTO issue_6710 (a, b) VALUES (1, 'test1'), (2, 'test2') 393 394 query IT rowsort 395 SELECT a, b from issue_6710 396 ---- 397 1 test1 398 2 test2 399 400 statement ok 401 CREATE TABLE issue_13962 (a INT PRIMARY KEY, b INT, c INT) 402 403 statement count 1 404 INSERT INTO issue_13962 VALUES (1, 1, 1) 405 406 statement count 1 407 INSERT INTO issue_13962 VALUES (1, 2, 2) ON CONFLICT (a) DO UPDATE SET b = excluded.b 408 409 query III 410 SELECT * FROM issue_13962 411 ---- 412 1 2 1 413 414 statement ok 415 CREATE TABLE issue_14052 (a INT PRIMARY KEY, b INT, c INT) 416 417 statement count 2 418 INSERT INTO issue_14052 (a, b) VALUES (1, 1), (2, 2) 419 420 statement count 2 421 UPSERT INTO issue_14052 (a, c) (SELECT a, b from issue_14052) 422 423 statement ok 424 CREATE TABLE issue_14052_2 ( 425 id SERIAL PRIMARY KEY, 426 name VARCHAR(255), 427 createdAt INT, 428 updatedAt INT 429 ) 430 431 statement count 1 432 INSERT INTO issue_14052_2 (id, name, createdAt, updatedAt) VALUES 433 (1, 'original', 1, 1) 434 435 # Make sure the fast path isn't taken (createdAt is not in the ON CONFLICT clause) 436 statement count 1 437 INSERT INTO issue_14052_2 (id, name, createdAt, updatedAt) VALUES 438 (1, 'UPDATED', 2, 2) 439 ON CONFLICT (id) DO UPDATE 440 SET id = excluded.id, name = excluded.name, updatedAt = excluded.updatedAt 441 442 query ITII 443 SELECT * FROM issue_14052_2; 444 ---- 445 1 UPDATED 1 2 446 447 statement error multiple assignments to the same column 448 INSERT INTO issue_14052_2 (id, name, createdAt, updatedAt) VALUES 449 (1, 'FOO', 3, 3) 450 ON CONFLICT (id) DO UPDATE 451 SET id = excluded.id, name = excluded.name, name = excluded.name, name = excluded.name 452 453 # Make sure the fast path isn't taken (all clauses in the set must be of the form x = excluded.x) 454 statement count 1 455 INSERT INTO issue_14052_2 (id, name, createdAt, updatedAt) VALUES 456 (1, 'BAR', 4, 5) 457 ON CONFLICT (id) DO UPDATE 458 SET name = excluded.name, createdAt = excluded.updatedAt, updatedAt = excluded.updatedAt 459 460 query ITII 461 SELECT * FROM issue_14052_2; 462 ---- 463 1 BAR 5 5 464 465 # Make sure the column types are propagated when type checking the ON CONFLICT 466 # expressions. See #16873. 467 statement ok 468 CREATE TABLE issue_16873 (col int PRIMARY KEY, date TIMESTAMP); 469 470 # n.b. the fully-qualified names below are required, as there are two providers of 471 # the column named `col` here, the original table and the `excluded` pseudo-table. 472 statement count 1 473 INSERT INTO issue_16873 VALUES (1,clock_timestamp()) 474 ON CONFLICT (col) DO UPDATE SET date = clock_timestamp() WHERE issue_16873.col = 1; 475 476 statement count 1 477 INSERT INTO issue_16873 VALUES (1,clock_timestamp()) 478 ON CONFLICT (col) DO UPDATE SET date = clock_timestamp() WHERE issue_16873.col = 1; 479 480 # For #17339. Support WHERE clause in ON CONFLICT handling. 481 statement ok 482 CREATE TABLE issue_17339 (a int primary key, b int); 483 484 statement count 2 485 INSERT INTO issue_17339 VALUES (1, 1), (2, 0); 486 487 statement count 1 488 INSERT INTO issue_17339 VALUES (1, 0), (2, 2) 489 ON CONFLICT (a) DO UPDATE SET b = excluded.b WHERE excluded.b > issue_17339.b; 490 491 query II 492 SELECT * FROM issue_17339 ORDER BY a; 493 ---- 494 1 1 495 2 2 496 497 statement count 2 498 INSERT INTO issue_17339 VALUES (1, 0), (2, 1) 499 ON CONFLICT (a) DO UPDATE SET b = excluded.b WHERE TRUE; 500 501 query II 502 SELECT * FROM issue_17339 ORDER BY a; 503 ---- 504 1 0 505 2 1 506 507 # Regression test for #25726. 508 # UPSERT over tables with column families, on the fast path, use the 509 # INSERT logic. This has special casing for column families of 1 510 # column, and another special casing for column families of 2+ 511 # columns. The special casing is only for families that do not include 512 # the primary key. So we need a table with 3 families: 1 for the PK, 1 513 # with just 1 col, and 1 with 2+ cols. 514 statement ok 515 CREATE TABLE tu (a INT PRIMARY KEY, b INT, c INT, d INT, FAMILY (a), FAMILY (b), FAMILY (c,d)); 516 INSERT INTO tu VALUES (1, 2, 3, 4) 517 518 statement ok 519 UPSERT INTO tu VALUES (1, NULL, NULL, NULL) 520 521 query IIII rowsort 522 SELECT * FROM tu 523 ---- 524 1 NULL NULL NULL 525 526 subtest check 527 528 statement ok 529 CREATE TABLE ab( 530 a INT PRIMARY KEY, 531 b INT, CHECK (b < 1) 532 ) 533 534 statement count 1 535 INSERT INTO ab(a, b) VALUES (1, 0); 536 537 statement error pq: failed to satisfy CHECK constraint \(b < 1:::INT8\) 538 INSERT INTO ab(a, b) VALUES (1, 0) ON CONFLICT(a) DO UPDATE SET b=12312313; 539 540 statement count 1 541 INSERT INTO ab(a, b) VALUES (1, 0) ON CONFLICT(a) DO UPDATE SET b=-1; 542 543 statement ok 544 CREATE TABLE abc_check( 545 a INT PRIMARY KEY, 546 b INT, 547 c INT, 548 CHECK (b < 1), 549 CHECK (c > 1) 550 ) 551 552 statement count 1 553 INSERT INTO abc_check(a, b, c) VALUES (1, 0, 2); 554 555 statement error pq: failed to satisfy CHECK constraint \(b < 1:::INT8\) 556 INSERT INTO abc_check(a, b, c) VALUES (1, 0, 2) ON CONFLICT(a) DO UPDATE SET b=12312313; 557 558 statement error pq: failed to satisfy CHECK constraint \(b < 1:::INT8\) 559 INSERT INTO abc_check(a, b, c) VALUES (1, 0, 2) ON CONFLICT(a) DO UPDATE SET (b, c) = (1, 1); 560 561 statement error pq: failed to satisfy CHECK constraint \(c > 1:::INT8\) 562 INSERT INTO abc_check(a, b, c) VALUES (1, 0, 2) ON CONFLICT(a) DO UPDATE SET (b, c) = (-1, 1); 563 564 statement count 1 565 INSERT INTO abc_check(a, b, c) VALUES (2, 0, 3); 566 567 statement error pq: failed to satisfy CHECK constraint \(b < 1:::INT8\) 568 INSERT INTO abc_check(c, a, b) VALUES (3, 2, 0) ON CONFLICT(a) DO UPDATE SET b=12312313; 569 570 statement error pq: failed to satisfy CHECK constraint \(b < 1:::INT8\) 571 INSERT INTO abc_check(a, c) VALUES (2, 3) ON CONFLICT(a) DO UPDATE SET b=12312313; 572 573 statement error pq: failed to satisfy CHECK constraint \(c > 1:::INT8\) 574 INSERT INTO abc_check(a, c) VALUES (2, 3) ON CONFLICT(a) DO UPDATE SET c=1; 575 576 statement error pq: failed to satisfy CHECK constraint \(c > 1:::INT8\) 577 INSERT INTO abc_check(c, a) VALUES (3, 2) ON CONFLICT(a) DO UPDATE SET c=1; 578 579 statement error pq: failed to satisfy CHECK constraint \(b < 1:::INT8\) 580 INSERT INTO abc_check(c, a) VALUES (3, 2) ON CONFLICT(a) DO UPDATE SET b=123123123; 581 582 statement error pq: failed to satisfy CHECK constraint \(b < 1:::INT8\) 583 INSERT INTO abc_check(c, a) VALUES (3, 2) ON CONFLICT(a) DO UPDATE SET b=123123123; 584 585 subtest 29495 586 587 statement ok 588 CREATE TABLE IF NOT EXISTS example ( 589 id SERIAL PRIMARY KEY 590 ,value string NOT NULL 591 ); 592 593 query B 594 UPSERT INTO example (value) VALUES ('foo') RETURNING id > 0 595 ---- 596 true 597 598 statement ok 599 DROP TABLE example 600 601 subtest contraint_check_validation_ordering 602 603 # Verification of column constraints vs CHECK handling. The column 604 # constraint verification must take place first. 605 # 606 # This test requires that the error message for a CHECK constraint 607 # validation error be different than a column validation error. So we 608 # test the former first, as a sanity check. 609 statement ok 610 CREATE TABLE tn(x INT NULL CHECK(x IS NOT NULL), y CHAR(4) CHECK(length(y) < 4)); 611 612 statement error failed to satisfy CHECK constraint 613 UPSERT INTO tn(x) VALUES (NULL) 614 615 statement error failed to satisfy CHECK constraint 616 UPSERT INTO tn(y) VALUES ('abcd') 617 618 # Now we test that the column validation occurs before the CHECK constraint. 619 statement ok 620 CREATE TABLE tn2(x INT NOT NULL CHECK(x IS NOT NULL), y CHAR(3) CHECK(length(y) < 4)); 621 622 statement error null value in column "x" violates not-null constraint 623 UPSERT INTO tn2(x) VALUES (NULL) 624 625 statement error value too long for type CHAR\(3\) 626 UPSERT INTO tn2(x, y) VALUES (123, 'abcd') 627 628 subtest regression_29494 629 630 statement ok 631 CREATE TABLE t29494(x INT); INSERT INTO t29494 VALUES (12) 632 633 statement ok 634 BEGIN; ALTER TABLE t29494 ADD COLUMN y INT NOT NULL DEFAULT 123 635 636 # Check that the new column is not visible 637 query T 638 SELECT create_statement FROM [SHOW CREATE t29494] 639 ---- 640 CREATE TABLE t29494 ( 641 x INT8 NULL, 642 FAMILY "primary" (x, rowid) 643 ) 644 645 # Check that the new column is not usable in RETURNING 646 statement error column "y" does not exist 647 UPSERT INTO t29494(x) VALUES (123) RETURNING y 648 649 # Ditto for INSERT ON CONFLICT 650 statement ok 651 ROLLBACK; BEGIN; ALTER TABLE t29494 ADD COLUMN y INT NOT NULL DEFAULT 123 652 653 statement error column "y" does not exist 654 INSERT INTO t29494(x) VALUES (123) ON CONFLICT(rowid) DO UPDATE SET x = 400 RETURNING y 655 656 statement ok 657 ROLLBACK 658 659 statement ok 660 BEGIN; ALTER TABLE t29494 ADD COLUMN y INT NOT NULL DEFAULT 123 661 662 query I 663 UPSERT INTO t29494(x) VALUES (12) RETURNING * 664 ---- 665 12 666 667 query I 668 UPSERT INTO t29494(x) VALUES (123) RETURNING * 669 ---- 670 123 671 672 query I 673 INSERT INTO t29494(x) VALUES (123) ON CONFLICT(rowid) DO UPDATE SET x = 400 RETURNING * 674 ---- 675 123 676 677 statement ok 678 COMMIT 679 680 subtest regression_31255 681 682 statement ok 683 CREATE TABLE tc(x INT PRIMARY KEY, y INT AS (x+1) STORED) 684 685 statement error cannot write directly to computed column "y" 686 INSERT INTO tc(x) VALUES (1) ON CONFLICT(x) DO UPDATE SET y = 123 687 688 statement error cannot write directly to computed column "y" 689 UPSERT INTO tc(x,y) VALUES (1,2) 690 691 statement error cannot write directly to computed column "y" 692 UPSERT INTO tc VALUES (1,2) 693 694 subtest regression_29497 695 696 statement ok 697 CREATE TABLE t29497(x INT PRIMARY KEY); BEGIN; ALTER TABLE t29497 ADD COLUMN y INT NOT NULL DEFAULT 123 698 699 statement error UPSERT has more expressions than target columns 700 UPSERT INTO t29497 VALUES (1, 2) 701 702 statement ok 703 ROLLBACK; BEGIN; ALTER TABLE t29497 ADD COLUMN y INT NOT NULL DEFAULT 123 704 705 statement error column "y" does not exist 706 INSERT INTO t29497(x) VALUES (1) ON CONFLICT (x) DO UPDATE SET y = 456 707 708 statement ok 709 ROLLBACK 710 711 subtest visible_returning_columns 712 713 statement ok 714 BEGIN; ALTER TABLE tc DROP COLUMN y 715 716 query I colnames rowsort 717 UPSERT INTO tc VALUES (1), (2) RETURNING * 718 ---- 719 x 720 1 721 2 722 723 statement ok 724 COMMIT 725 726 subtest regression_32762 727 728 statement ok 729 CREATE TABLE t32762(x INT, y INT, UNIQUE (x,y), CONSTRAINT y_not_null CHECK (y IS NOT NULL)) 730 731 statement ok 732 INSERT INTO t32762(x,y) VALUES (1,2) ON CONFLICT (x,y) DO UPDATE SET x = t32762.x; 733 734 statement ok 735 INSERT INTO t32762(x,y) VALUES (1,2) ON CONFLICT (x,y) DO UPDATE SET x = t32762.x 736 737 subtest regression_33313 738 739 statement ok 740 CREATE TABLE ex33313(foo INT PRIMARY KEY, bar INT UNIQUE, baz INT); 741 INSERT INTO ex33313 VALUES (1,1,1); 742 743 statement count 1 744 INSERT INTO ex33313(foo,bar,baz) VALUES (1,2,1), (3,2,2) ON CONFLICT DO NOTHING; 745 746 query III colnames 747 SELECT * FROM ex33313 ORDER BY foo 748 ---- 749 foo bar baz 750 1 1 1 751 3 2 2 752 753 # Use Upsert with indexed table, default columns, computed columns, and check 754 # columns. 755 statement ok 756 CREATE TABLE indexed ( 757 a DECIMAL PRIMARY KEY, 758 b DECIMAL, 759 c DECIMAL DEFAULT(10.0), 760 d DECIMAL AS (a + c) STORED, 761 UNIQUE INDEX secondary (d, b), 762 CHECK (c > 0) 763 ) 764 765 statement ok 766 INSERT INTO indexed VALUES (1, 1, 1); INSERT INTO indexed VALUES (2, 2, 2) 767 768 # Use implicit target columns (should set default and computed values). 769 statement ok 770 UPSERT INTO indexed VALUES (1.0) 771 772 query TTTT colnames 773 SELECT * FROM indexed@secondary ORDER BY d, b 774 ---- 775 a b c d 776 2 2 2 4 777 1 NULL 10.0 11.0 778 779 # Explicitly specify all target columns. Ensure that primary key is not updated, 780 # even though an alternate but equal decimal form is in use (1.0 vs. 1). 781 statement ok 782 UPSERT INTO indexed (a, b, c) VALUES (1.0, 1.0, 1.0) 783 784 query TTTT colnames 785 SELECT * FROM indexed@secondary ORDER BY d, b 786 ---- 787 a b c d 788 1 1.0 1.0 2.0 789 2 2 2 4 790 791 # Ensure that explicit target column does not disturb existing "b" value, but 792 # does update the computed column. 793 statement ok 794 UPSERT INTO indexed (c, a) VALUES (2, 1) 795 796 query TTTT colnames 797 SELECT * FROM indexed@secondary ORDER BY d, b 798 ---- 799 a b c d 800 1 1.0 2 3 801 2 2 2 4 802 803 # Final check to ensure that primary index is correct. 804 query TTTT colnames 805 SELECT * FROM indexed@primary ORDER BY a 806 ---- 807 a b c d 808 1 1.0 2 3 809 2 2 2 4 810 811 # Drop the secondary index, allowing the "blind upsert" path to run. 812 statement ok 813 DROP INDEX indexed@secondary CASCADE 814 815 # Use implicit target columns (should set default and computed values). 816 statement ok 817 UPSERT INTO indexed VALUES (1, 1) 818 819 query TTTT colnames,rowsort 820 SELECT * FROM indexed 821 ---- 822 a b c d 823 1 1 10.0 11.0 824 2 2 2 4 825 826 # Explicitly specify all target columns. 827 statement ok 828 UPSERT INTO indexed (a, b, c) SELECT 1, 2, 3 829 830 query TTTT colnames,rowsort 831 SELECT * FROM indexed 832 ---- 833 a b c d 834 2 2 2 4 835 1 2 3 4 836 837 # Ensure that explicit target column does not disturb existing "b" value, but 838 # does update the computed column. 839 query TTTT 840 UPSERT INTO indexed (c, a) VALUES (2.0, 1.0) RETURNING * 841 ---- 842 1 2 2.0 3.0 843 844 query TTTT colnames,rowsort 845 SELECT * FROM indexed 846 ---- 847 a b c d 848 1 2 2.0 3.0 849 2 2 2 4 850 851 statement ok 852 DROP TABLE indexed 853 854 subtest regression_35040 855 856 statement ok 857 CREATE TABLE test35040(a INT PRIMARY KEY, b INT NOT NULL, c INT2) 858 859 statement ok 860 INSERT INTO test35040(a,b) VALUES(0,0) ON CONFLICT(a) DO UPDATE SET b = NULL 861 862 statement error null value in column "b" violates not-null constraint 863 INSERT INTO test35040(a,b) VALUES(0,0) ON CONFLICT(a) DO UPDATE SET b = NULL 864 865 statement error integer out of range for type int2 866 INSERT INTO test35040(a,b) VALUES (0,1) ON CONFLICT(a) DO UPDATE SET c = 111111111; 867 868 statement ok 869 DROP TABLE test35040 870 871 # ------------------------------------------------------------------------------ 872 # Regression for #35364. 873 # ------------------------------------------------------------------------------ 874 subtest regression_35364 875 876 statement ok 877 CREATE TABLE t35364(x INT PRIMARY KEY, y DECIMAL(10,1) CHECK(y >= 8.0), UNIQUE INDEX (y)) 878 879 statement ok 880 INSERT INTO t35364(x, y) VALUES (1, 10.2) 881 882 # 10.18 should be mapped to 10.2 before the left outer join so that the conflict 883 # can be detected, and 7.95 should be mapped to 8.0 so that check constraint 884 # will pass. 885 statement ok 886 INSERT INTO t35364(x, y) VALUES (2, 10.18) ON CONFLICT (y) DO UPDATE SET y=7.95 887 888 query IT 889 SELECT * FROM t35364 890 ---- 891 1 8.0 892 893 statement ok 894 DROP TABLE t35364 895 896 # Check UPSERT syntax. 897 statement ok 898 CREATE TABLE t35364( 899 x DECIMAL(10,0) CHECK (x >= 0) PRIMARY KEY, 900 y DECIMAL(10,0) CHECK (y >= 0) 901 ) 902 903 statement ok 904 UPSERT INTO t35364 (x) VALUES (-0.1) 905 906 query TT 907 SELECT * FROM t35364 908 ---- 909 -0 NULL 910 911 statement ok 912 UPSERT INTO t35364 (x, y) VALUES (-0.2, -0.3) 913 914 query TT 915 SELECT * FROM t35364 916 ---- 917 -0 -0 918 919 statement ok 920 UPSERT INTO t35364 (x, y) VALUES (1.5, 2.5) 921 922 query TT rowsort 923 SELECT * FROM t35364 924 ---- 925 -0 -0 926 2 3 927 928 statement ok 929 INSERT INTO t35364 (x) VALUES (1.5) ON CONFLICT (x) DO UPDATE SET x=2.5, y=3.5 930 931 query TT rowsort 932 SELECT * FROM t35364 933 ---- 934 -0 -0 935 3 4 936 937 # ------------------------------------------------------------------------------ 938 # Regression for #35970. 939 # ------------------------------------------------------------------------------ 940 statement ok 941 CREATE TABLE table35970 ( 942 a DECIMAL(10,1) PRIMARY KEY, 943 b DECIMAL(10,1), 944 c DECIMAL(10,0), 945 FAMILY fam0 (a, b), 946 FAMILY fam1 (c) 947 ) 948 949 query I 950 UPSERT INTO table35970 (a) VALUES (1.5) RETURNING b 951 ---- 952 NULL 953 954 query I 955 INSERT INTO table35970 VALUES (1.5, 1.5, NULL) 956 ON CONFLICT (a) 957 DO UPDATE SET c = table35970.a+1 958 RETURNING b 959 ---- 960 NULL 961 962 # ------------------------------------------------------------------------------ 963 # Regression for #38627: make sure that UPSERTs in the presence of column 964 # mutations don't cause problems. 965 # ------------------------------------------------------------------------------ 966 967 statement ok 968 CREATE TABLE table38627 (a INT PRIMARY KEY, b INT); INSERT INTO table38627 VALUES(1,1) 969 970 statement ok 971 BEGIN; ALTER TABLE table38627 ADD COLUMN c INT NOT NULL DEFAULT 5 972 973 statement ok 974 UPSERT INTO table38627 SELECT * FROM table38627 WHERE a=1 975 976 query II 977 SELECT * from table38627 978 ---- 979 1 1 980 981 statement ok 982 COMMIT 983 984 query III 985 SELECT * from table38627 986 ---- 987 1 1 5 988 989 # ------------------------------------------------------------------------------ 990 # Regression for #44466. 991 # ------------------------------------------------------------------------------ 992 statement ok 993 CREATE TABLE t44466 (c0 INT PRIMARY KEY, c1 BOOL, c2 INT UNIQUE) 994 995 statement ok 996 INSERT INTO t44466 (c0) VALUES (0) 997 998 statement error pq: UPSERT or INSERT...ON CONFLICT command cannot affect row a second time 999 UPSERT INTO t44466 (c2, c0) VALUES (0, 0), (1, 0) 1000 1001 statement ok 1002 DROP TABLE t44466 1003 1004 # ------------------------------------------------------------------------------ 1005 # Regression for #46395. 1006 # ------------------------------------------------------------------------------ 1007 statement ok 1008 CREATE TABLE t46395(c0 INT UNIQUE DEFAULT 0, c1 INT); 1009 1010 statement ok 1011 INSERT INTO t46395(c1) VALUES (0), (1) ON CONFLICT (c0) DO NOTHING; 1012 1013 statement ok 1014 DROP TABLE t46395 1015 1016 # ------------------------------------------------------------------------------ 1017 # Duplicate primary key inputs. 1018 # ------------------------------------------------------------------------------ 1019 # Start with no secondary index present. 1020 statement ok 1021 CREATE TABLE tdup (x INT PRIMARY KEY, y INT, z INT) 1022 1023 statement ok 1024 INSERT INTO tdup VALUES (1, 1, 1) 1025 1026 statement error pq: UPSERT or INSERT...ON CONFLICT command cannot affect row a second time 1027 INSERT INTO tdup VALUES (1, 2, 1), (1, 3, 1) ON CONFLICT (x) DO UPDATE SET z=1 1028 1029 # Add secondary index and verify that same error occurs. 1030 statement ok 1031 CREATE UNIQUE INDEX ON tdup (y) 1032 1033 statement error pq: UPSERT or INSERT...ON CONFLICT command cannot affect row a second time 1034 INSERT INTO tdup VALUES (1, 2, 1), (1, 3, 1) ON CONFLICT (x) DO UPDATE SET z=1 1035 1036 # Verify that duplicate insert into secondary fails with regular conflict error. 1037 statement error pq: duplicate key value \(y\)=\(2\) violates unique constraint "tdup_y_key" 1038 INSERT INTO tdup VALUES (2, 2, 2), (3, 2, 2) ON CONFLICT (x) DO UPDATE SET z=1 1039 1040 statement ok 1041 DROP TABLE tdup 1042 1043 # ------------------------------------------------------------------------------ 1044 # Duplicate secondary key inputs. 1045 # ------------------------------------------------------------------------------ 1046 statement ok 1047 CREATE TABLE tdup (x INT PRIMARY KEY, y INT, z INT, UNIQUE (y, z)) 1048 1049 statement ok 1050 INSERT INTO tdup VALUES (1, 1, 1) 1051 1052 statement error pq: UPSERT or INSERT...ON CONFLICT command cannot affect row a second time 1053 INSERT INTO tdup VALUES (2, 2, 2), (3, 2, 2) ON CONFLICT (z, y) DO UPDATE SET z=1 1054 1055 # Verify that NULL values are not treated as duplicates. 1056 statement ok 1057 INSERT INTO tdup 1058 VALUES (2, 2, NULL), (3, 2, NULL), (4, NULL, NULL), (5, NULL, NULL) 1059 ON CONFLICT (z, y) DO UPDATE SET z=1 1060 1061 query III rowsort 1062 SELECT * FROM tdup 1063 ---- 1064 1 1 1 1065 2 2 NULL 1066 3 2 NULL 1067 4 NULL NULL 1068 5 NULL NULL 1069 1070 query III rowsort 1071 SELECT * FROM tdup@tdup_y_z_key 1072 ---- 1073 4 NULL NULL 1074 5 NULL NULL 1075 1 1 1 1076 2 2 NULL 1077 3 2 NULL 1078 1079 # Verify that duplicate secondary key fails with regular conflict error. 1080 statement error pq: duplicate key value \(y,z\)=\(1,2\) violates unique constraint "tdup_y_z_key" 1081 INSERT INTO tdup VALUES (6, 1, 1), (7, 1, 2) ON CONFLICT (y, z) DO UPDATE SET z=2 1082 1083 # With constant grouping columns (no error). 1084 statement ok 1085 INSERT INTO tdup SELECT 6, 2, z FROM tdup WHERE z=1 1086 ON CONFLICT (y, z) DO UPDATE SET z=2 1087 1088 query III rowsort 1089 SELECT * FROM tdup 1090 ---- 1091 1 1 1 1092 2 2 NULL 1093 3 2 NULL 1094 4 NULL NULL 1095 5 NULL NULL 1096 6 2 1 1097 1098 # With constant grouping columns (error). 1099 statement error pq: UPSERT or INSERT...ON CONFLICT command cannot affect row a second time 1100 INSERT INTO tdup SELECT 6, 2, z FROM tdup WHERE z=1 1101 ON CONFLICT (y, z) DO UPDATE SET z=2 1102 1103 # With constant nullable grouping columns. 1104 statement error pq: UPSERT or INSERT...ON CONFLICT command cannot affect row a second time 1105 INSERT INTO tdup SELECT x+100, y, y+1 FROM tdup WHERE z IS NULL 1106 ON CONFLICT (y, z) DO UPDATE SET z=3 1107 1108 statement ok 1109 DROP TABLE tdup 1110 1111 # ------------------------------------------------------------------------------ 1112 # Duplicate ordered inputs (use streaming distinct). 1113 # Ensure this test stays synchronized to the EXPLAIN in exec/execbuilder/upsert, 1114 # so that use of streaming group-by is confirmed. 1115 # ------------------------------------------------------------------------------ 1116 statement ok 1117 CREATE TABLE target (a INT PRIMARY KEY, b INT, c INT, UNIQUE (b, c)) 1118 1119 statement ok 1120 CREATE TABLE source (x INT PRIMARY KEY, y INT, z INT, INDEX (y, z)) 1121 1122 statement ok 1123 INSERT INTO source 1124 VALUES (1, 1, 1), (2, 1, 1), (3, 1, NULL), (4, 1, NULL), (5, NULL, NULL), (6, NULL, NULL) 1125 1126 # This upsert statement triggers streaming distinct by using conflict columns 1127 # (b,c) that are provided by the (y,z) index. 1128 statement error pq: UPSERT or INSERT...ON CONFLICT command cannot affect row a second time 1129 INSERT INTO target SELECT x, y, z FROM source ON CONFLICT (b, c) DO UPDATE SET b=5 1130 1131 # Ensure that NULL values are treated as distinct. 1132 statement ok 1133 INSERT INTO target SELECT x, y, z FROM source WHERE (y IS NULL OR y > 0) AND x <> 1 1134 ON CONFLICT (b, c) DO UPDATE SET b=5 1135 1136 query III rowsort 1137 SELECT * FROM target 1138 ---- 1139 2 1 1 1140 3 1 NULL 1141 4 1 NULL 1142 5 NULL NULL 1143 6 NULL NULL 1144 1145 query III rowsort 1146 SELECT * FROM target@target_b_c_key 1147 ---- 1148 5 NULL NULL 1149 6 NULL NULL 1150 3 1 NULL 1151 4 1 NULL 1152 2 1 1 1153 1154 statement ok 1155 DROP TABLE source 1156 1157 statement ok 1158 DROP TABLE target 1159 1160 # ------------------------------------------------------------------------------ 1161 # DO NOTHING with duplicate inputs. 1162 # ------------------------------------------------------------------------------ 1163 statement ok 1164 CREATE TABLE target (x INT PRIMARY KEY, y INT, z INT, UNIQUE (y, z)) 1165 1166 statement ok 1167 CREATE TABLE source (a INT, b INT, c INT) 1168 1169 # Ensure that duplicates are removed from the input at runtime. NULL values are 1170 # never considered to be duplicates. 1171 statement ok 1172 INSERT INTO source 1173 VALUES 1174 (1, 1, 2), 1175 (1, 2, 1), 1176 (1, 2, 2), 1177 (2, 3, 3), 1178 (4, 1, NULL), 1179 (5, 1, NULL), 1180 (6, NULL, NULL), 1181 (7, NULL, NULL), 1182 (3, 3, 3) 1183 1184 statement ok 1185 INSERT INTO target SELECT * FROM source ON CONFLICT DO NOTHING 1186 1187 query III rowsort 1188 SELECT * FROM target 1189 ---- 1190 1 1 2 1191 2 3 3 1192 4 1 NULL 1193 5 1 NULL 1194 6 NULL NULL 1195 7 NULL NULL 1196 1197 statement ok 1198 INSERT INTO target SELECT 8, y, z FROM (VALUES (2, 2), (2, 3)) s(y, z) 1199 ON CONFLICT (x) DO NOTHING 1200 1201 query III rowsort 1202 SELECT * FROM target 1203 ---- 1204 1 1 2 1205 2 3 3 1206 4 1 NULL 1207 5 1 NULL 1208 6 NULL NULL 1209 7 NULL NULL 1210 8 2 2 1211 1212 statement ok 1213 DROP TABLE source 1214 1215 statement ok 1216 DROP TABLE target