1-- 2-- Test inheritance features 3-- 4CREATE TABLE a (aa TEXT); 5CREATE TABLE b (bb TEXT) INHERITS (a); 6CREATE TABLE c (cc TEXT) INHERITS (a); 7CREATE TABLE d (dd TEXT) INHERITS (b,c,a); 8NOTICE: merging multiple inherited definitions of column "aa" 9NOTICE: merging multiple inherited definitions of column "aa" 10INSERT INTO a(aa) VALUES('aaa'); 11INSERT INTO a(aa) VALUES('aaaa'); 12INSERT INTO a(aa) VALUES('aaaaa'); 13INSERT INTO a(aa) VALUES('aaaaaa'); 14INSERT INTO a(aa) VALUES('aaaaaaa'); 15INSERT INTO a(aa) VALUES('aaaaaaaa'); 16INSERT INTO b(aa) VALUES('bbb'); 17INSERT INTO b(aa) VALUES('bbbb'); 18INSERT INTO b(aa) VALUES('bbbbb'); 19INSERT INTO b(aa) VALUES('bbbbbb'); 20INSERT INTO b(aa) VALUES('bbbbbbb'); 21INSERT INTO b(aa) VALUES('bbbbbbbb'); 22INSERT INTO c(aa) VALUES('ccc'); 23INSERT INTO c(aa) VALUES('cccc'); 24INSERT INTO c(aa) VALUES('ccccc'); 25INSERT INTO c(aa) VALUES('cccccc'); 26INSERT INTO c(aa) VALUES('ccccccc'); 27INSERT INTO c(aa) VALUES('cccccccc'); 28INSERT INTO d(aa) VALUES('ddd'); 29INSERT INTO d(aa) VALUES('dddd'); 30INSERT INTO d(aa) VALUES('ddddd'); 31INSERT INTO d(aa) VALUES('dddddd'); 32INSERT INTO d(aa) VALUES('ddddddd'); 33INSERT INTO d(aa) VALUES('dddddddd'); 34SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; 35 relname | aa 36---------+---------- 37 a | aaa 38 a | aaaa 39 a | aaaaa 40 a | aaaaaa 41 a | aaaaaaa 42 a | aaaaaaaa 43 b | bbb 44 b | bbbb 45 b | bbbbb 46 b | bbbbbb 47 b | bbbbbbb 48 b | bbbbbbbb 49 c | ccc 50 c | cccc 51 c | ccccc 52 c | cccccc 53 c | ccccccc 54 c | cccccccc 55 d | ddd 56 d | dddd 57 d | ddddd 58 d | dddddd 59 d | ddddddd 60 d | dddddddd 61(24 rows) 62 63SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; 64 relname | aa | bb 65---------+----------+---- 66 b | bbb | 67 b | bbbb | 68 b | bbbbb | 69 b | bbbbbb | 70 b | bbbbbbb | 71 b | bbbbbbbb | 72 d | ddd | 73 d | dddd | 74 d | ddddd | 75 d | dddddd | 76 d | ddddddd | 77 d | dddddddd | 78(12 rows) 79 80SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid; 81 relname | aa | cc 82---------+----------+---- 83 c | ccc | 84 c | cccc | 85 c | ccccc | 86 c | cccccc | 87 c | ccccccc | 88 c | cccccccc | 89 d | ddd | 90 d | dddd | 91 d | ddddd | 92 d | dddddd | 93 d | ddddddd | 94 d | dddddddd | 95(12 rows) 96 97SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid; 98 relname | aa | bb | cc | dd 99---------+----------+----+----+---- 100 d | ddd | | | 101 d | dddd | | | 102 d | ddddd | | | 103 d | dddddd | | | 104 d | ddddddd | | | 105 d | dddddddd | | | 106(6 rows) 107 108SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; 109 relname | aa 110---------+---------- 111 a | aaa 112 a | aaaa 113 a | aaaaa 114 a | aaaaaa 115 a | aaaaaaa 116 a | aaaaaaaa 117(6 rows) 118 119SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid; 120 relname | aa | bb 121---------+----------+---- 122 b | bbb | 123 b | bbbb | 124 b | bbbbb | 125 b | bbbbbb | 126 b | bbbbbbb | 127 b | bbbbbbbb | 128(6 rows) 129 130SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid; 131 relname | aa | cc 132---------+----------+---- 133 c | ccc | 134 c | cccc | 135 c | ccccc | 136 c | cccccc | 137 c | ccccccc | 138 c | cccccccc | 139(6 rows) 140 141SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid; 142 relname | aa | bb | cc | dd 143---------+----------+----+----+---- 144 d | ddd | | | 145 d | dddd | | | 146 d | ddddd | | | 147 d | dddddd | | | 148 d | ddddddd | | | 149 d | dddddddd | | | 150(6 rows) 151 152UPDATE a SET aa='zzzz' WHERE aa='aaaa'; 153UPDATE ONLY a SET aa='zzzzz' WHERE aa='aaaaa'; 154UPDATE b SET aa='zzz' WHERE aa='aaa'; 155UPDATE ONLY b SET aa='zzz' WHERE aa='aaa'; 156UPDATE a SET aa='zzzzzz' WHERE aa LIKE 'aaa%'; 157SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; 158 relname | aa 159---------+---------- 160 a | zzzz 161 a | zzzzz 162 a | zzzzzz 163 a | zzzzzz 164 a | zzzzzz 165 a | zzzzzz 166 b | bbb 167 b | bbbb 168 b | bbbbb 169 b | bbbbbb 170 b | bbbbbbb 171 b | bbbbbbbb 172 c | ccc 173 c | cccc 174 c | ccccc 175 c | cccccc 176 c | ccccccc 177 c | cccccccc 178 d | ddd 179 d | dddd 180 d | ddddd 181 d | dddddd 182 d | ddddddd 183 d | dddddddd 184(24 rows) 185 186SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; 187 relname | aa | bb 188---------+----------+---- 189 b | bbb | 190 b | bbbb | 191 b | bbbbb | 192 b | bbbbbb | 193 b | bbbbbbb | 194 b | bbbbbbbb | 195 d | ddd | 196 d | dddd | 197 d | ddddd | 198 d | dddddd | 199 d | ddddddd | 200 d | dddddddd | 201(12 rows) 202 203SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid; 204 relname | aa | cc 205---------+----------+---- 206 c | ccc | 207 c | cccc | 208 c | ccccc | 209 c | cccccc | 210 c | ccccccc | 211 c | cccccccc | 212 d | ddd | 213 d | dddd | 214 d | ddddd | 215 d | dddddd | 216 d | ddddddd | 217 d | dddddddd | 218(12 rows) 219 220SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid; 221 relname | aa | bb | cc | dd 222---------+----------+----+----+---- 223 d | ddd | | | 224 d | dddd | | | 225 d | ddddd | | | 226 d | dddddd | | | 227 d | ddddddd | | | 228 d | dddddddd | | | 229(6 rows) 230 231SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; 232 relname | aa 233---------+-------- 234 a | zzzz 235 a | zzzzz 236 a | zzzzzz 237 a | zzzzzz 238 a | zzzzzz 239 a | zzzzzz 240(6 rows) 241 242SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid; 243 relname | aa | bb 244---------+----------+---- 245 b | bbb | 246 b | bbbb | 247 b | bbbbb | 248 b | bbbbbb | 249 b | bbbbbbb | 250 b | bbbbbbbb | 251(6 rows) 252 253SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid; 254 relname | aa | cc 255---------+----------+---- 256 c | ccc | 257 c | cccc | 258 c | ccccc | 259 c | cccccc | 260 c | ccccccc | 261 c | cccccccc | 262(6 rows) 263 264SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid; 265 relname | aa | bb | cc | dd 266---------+----------+----+----+---- 267 d | ddd | | | 268 d | dddd | | | 269 d | ddddd | | | 270 d | dddddd | | | 271 d | ddddddd | | | 272 d | dddddddd | | | 273(6 rows) 274 275UPDATE b SET aa='new'; 276SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; 277 relname | aa 278---------+---------- 279 a | zzzz 280 a | zzzzz 281 a | zzzzzz 282 a | zzzzzz 283 a | zzzzzz 284 a | zzzzzz 285 b | new 286 b | new 287 b | new 288 b | new 289 b | new 290 b | new 291 c | ccc 292 c | cccc 293 c | ccccc 294 c | cccccc 295 c | ccccccc 296 c | cccccccc 297 d | new 298 d | new 299 d | new 300 d | new 301 d | new 302 d | new 303(24 rows) 304 305SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; 306 relname | aa | bb 307---------+-----+---- 308 b | new | 309 b | new | 310 b | new | 311 b | new | 312 b | new | 313 b | new | 314 d | new | 315 d | new | 316 d | new | 317 d | new | 318 d | new | 319 d | new | 320(12 rows) 321 322SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid; 323 relname | aa | cc 324---------+----------+---- 325 c | ccc | 326 c | cccc | 327 c | ccccc | 328 c | cccccc | 329 c | ccccccc | 330 c | cccccccc | 331 d | new | 332 d | new | 333 d | new | 334 d | new | 335 d | new | 336 d | new | 337(12 rows) 338 339SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid; 340 relname | aa | bb | cc | dd 341---------+-----+----+----+---- 342 d | new | | | 343 d | new | | | 344 d | new | | | 345 d | new | | | 346 d | new | | | 347 d | new | | | 348(6 rows) 349 350SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; 351 relname | aa 352---------+-------- 353 a | zzzz 354 a | zzzzz 355 a | zzzzzz 356 a | zzzzzz 357 a | zzzzzz 358 a | zzzzzz 359(6 rows) 360 361SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid; 362 relname | aa | bb 363---------+-----+---- 364 b | new | 365 b | new | 366 b | new | 367 b | new | 368 b | new | 369 b | new | 370(6 rows) 371 372SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid; 373 relname | aa | cc 374---------+----------+---- 375 c | ccc | 376 c | cccc | 377 c | ccccc | 378 c | cccccc | 379 c | ccccccc | 380 c | cccccccc | 381(6 rows) 382 383SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid; 384 relname | aa | bb | cc | dd 385---------+-----+----+----+---- 386 d | new | | | 387 d | new | | | 388 d | new | | | 389 d | new | | | 390 d | new | | | 391 d | new | | | 392(6 rows) 393 394UPDATE a SET aa='new'; 395DELETE FROM ONLY c WHERE aa='new'; 396SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; 397 relname | aa 398---------+----- 399 a | new 400 a | new 401 a | new 402 a | new 403 a | new 404 a | new 405 b | new 406 b | new 407 b | new 408 b | new 409 b | new 410 b | new 411 d | new 412 d | new 413 d | new 414 d | new 415 d | new 416 d | new 417(18 rows) 418 419SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; 420 relname | aa | bb 421---------+-----+---- 422 b | new | 423 b | new | 424 b | new | 425 b | new | 426 b | new | 427 b | new | 428 d | new | 429 d | new | 430 d | new | 431 d | new | 432 d | new | 433 d | new | 434(12 rows) 435 436SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid; 437 relname | aa | cc 438---------+-----+---- 439 d | new | 440 d | new | 441 d | new | 442 d | new | 443 d | new | 444 d | new | 445(6 rows) 446 447SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid; 448 relname | aa | bb | cc | dd 449---------+-----+----+----+---- 450 d | new | | | 451 d | new | | | 452 d | new | | | 453 d | new | | | 454 d | new | | | 455 d | new | | | 456(6 rows) 457 458SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; 459 relname | aa 460---------+----- 461 a | new 462 a | new 463 a | new 464 a | new 465 a | new 466 a | new 467(6 rows) 468 469SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid; 470 relname | aa | bb 471---------+-----+---- 472 b | new | 473 b | new | 474 b | new | 475 b | new | 476 b | new | 477 b | new | 478(6 rows) 479 480SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid; 481 relname | aa | cc 482---------+----+---- 483(0 rows) 484 485SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid; 486 relname | aa | bb | cc | dd 487---------+-----+----+----+---- 488 d | new | | | 489 d | new | | | 490 d | new | | | 491 d | new | | | 492 d | new | | | 493 d | new | | | 494(6 rows) 495 496DELETE FROM a; 497SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid; 498 relname | aa 499---------+---- 500(0 rows) 501 502SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid; 503 relname | aa | bb 504---------+----+---- 505(0 rows) 506 507SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid; 508 relname | aa | cc 509---------+----+---- 510(0 rows) 511 512SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid; 513 relname | aa | bb | cc | dd 514---------+----+----+----+---- 515(0 rows) 516 517SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid; 518 relname | aa 519---------+---- 520(0 rows) 521 522SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid; 523 relname | aa | bb 524---------+----+---- 525(0 rows) 526 527SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid; 528 relname | aa | cc 529---------+----+---- 530(0 rows) 531 532SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid; 533 relname | aa | bb | cc | dd 534---------+----+----+----+---- 535(0 rows) 536 537-- Confirm PRIMARY KEY adds NOT NULL constraint to child table 538CREATE TEMP TABLE z (b TEXT, PRIMARY KEY(aa, b)) inherits (a); 539INSERT INTO z VALUES (NULL, 'text'); -- should fail 540ERROR: null value in column "aa" violates not-null constraint 541DETAIL: Failing row contains (null, text). 542-- Check inherited UPDATE with all children excluded 543create table some_tab (a int, b int); 544create table some_tab_child () inherits (some_tab); 545insert into some_tab_child values(1,2); 546explain (verbose, costs off) 547update some_tab set a = a + 1 where false; 548 QUERY PLAN 549---------------------------------- 550 Update on public.some_tab 551 Update on public.some_tab 552 -> Result 553 Output: (a + 1), b, ctid 554 One-Time Filter: false 555(5 rows) 556 557update some_tab set a = a + 1 where false; 558explain (verbose, costs off) 559update some_tab set a = a + 1 where false returning b, a; 560 QUERY PLAN 561---------------------------------- 562 Update on public.some_tab 563 Output: b, a 564 Update on public.some_tab 565 -> Result 566 Output: (a + 1), b, ctid 567 One-Time Filter: false 568(6 rows) 569 570update some_tab set a = a + 1 where false returning b, a; 571 b | a 572---+--- 573(0 rows) 574 575table some_tab; 576 a | b 577---+--- 578 1 | 2 579(1 row) 580 581drop table some_tab cascade; 582NOTICE: drop cascades to table some_tab_child 583-- Check UPDATE with inherited target and an inherited source table 584create temp table foo(f1 int, f2 int); 585create temp table foo2(f3 int) inherits (foo); 586create temp table bar(f1 int, f2 int); 587create temp table bar2(f3 int) inherits (bar); 588insert into foo values(1,1); 589insert into foo values(3,3); 590insert into foo2 values(2,2,2); 591insert into foo2 values(3,3,3); 592insert into bar values(1,1); 593insert into bar values(2,2); 594insert into bar values(3,3); 595insert into bar values(4,4); 596insert into bar2 values(1,1,1); 597insert into bar2 values(2,2,2); 598insert into bar2 values(3,3,3); 599insert into bar2 values(4,4,4); 600update bar set f2 = f2 + 100 where f1 in (select f1 from foo); 601select tableoid::regclass::text as relname, bar.* from bar order by 1,2; 602 relname | f1 | f2 603---------+----+----- 604 bar | 1 | 101 605 bar | 2 | 102 606 bar | 3 | 103 607 bar | 4 | 4 608 bar2 | 1 | 101 609 bar2 | 2 | 102 610 bar2 | 3 | 103 611 bar2 | 4 | 4 612(8 rows) 613 614-- Check UPDATE with inherited target and an appendrel subquery 615update bar set f2 = f2 + 100 616from 617 ( select f1 from foo union all select f1+3 from foo ) ss 618where bar.f1 = ss.f1; 619select tableoid::regclass::text as relname, bar.* from bar order by 1,2; 620 relname | f1 | f2 621---------+----+----- 622 bar | 1 | 201 623 bar | 2 | 202 624 bar | 3 | 203 625 bar | 4 | 104 626 bar2 | 1 | 201 627 bar2 | 2 | 202 628 bar2 | 3 | 203 629 bar2 | 4 | 104 630(8 rows) 631 632-- Check UPDATE with *partitioned* inherited target and an appendrel subquery 633create table some_tab (a int); 634insert into some_tab values (0); 635create table some_tab_child () inherits (some_tab); 636insert into some_tab_child values (1); 637create table parted_tab (a int, b char) partition by list (a); 638create table parted_tab_part1 partition of parted_tab for values in (1); 639create table parted_tab_part2 partition of parted_tab for values in (2); 640create table parted_tab_part3 partition of parted_tab for values in (3); 641insert into parted_tab values (1, 'a'), (2, 'a'), (3, 'a'); 642update parted_tab set b = 'b' 643from 644 (select a from some_tab union all select a+1 from some_tab) ss (a) 645where parted_tab.a = ss.a; 646select tableoid::regclass::text as relname, parted_tab.* from parted_tab order by 1,2; 647 relname | a | b 648------------------+---+--- 649 parted_tab_part1 | 1 | b 650 parted_tab_part2 | 2 | b 651 parted_tab_part3 | 3 | a 652(3 rows) 653 654truncate parted_tab; 655insert into parted_tab values (1, 'a'), (2, 'a'), (3, 'a'); 656update parted_tab set b = 'b' 657from 658 (select 0 from parted_tab union all select 1 from parted_tab) ss (a) 659where parted_tab.a = ss.a; 660select tableoid::regclass::text as relname, parted_tab.* from parted_tab order by 1,2; 661 relname | a | b 662------------------+---+--- 663 parted_tab_part1 | 1 | b 664 parted_tab_part2 | 2 | a 665 parted_tab_part3 | 3 | a 666(3 rows) 667 668-- modifies partition key, but no rows will actually be updated 669explain update parted_tab set a = 2 where false; 670 QUERY PLAN 671-------------------------------------------------------- 672 Update on parted_tab (cost=0.00..0.00 rows=0 width=0) 673 -> Result (cost=0.00..0.00 rows=0 width=0) 674 One-Time Filter: false 675(3 rows) 676 677drop table parted_tab; 678drop table some_tab cascade; 679NOTICE: drop cascades to table some_tab_child 680/* Test multiple inheritance of column defaults */ 681CREATE TABLE firstparent (tomorrow date default now()::date + 1); 682CREATE TABLE secondparent (tomorrow date default now() :: date + 1); 683CREATE TABLE jointchild () INHERITS (firstparent, secondparent); -- ok 684NOTICE: merging multiple inherited definitions of column "tomorrow" 685CREATE TABLE thirdparent (tomorrow date default now()::date - 1); 686CREATE TABLE otherchild () INHERITS (firstparent, thirdparent); -- not ok 687NOTICE: merging multiple inherited definitions of column "tomorrow" 688ERROR: column "tomorrow" inherits conflicting default values 689HINT: To resolve the conflict, specify a default explicitly. 690CREATE TABLE otherchild (tomorrow date default now()) 691 INHERITS (firstparent, thirdparent); -- ok, child resolves ambiguous default 692NOTICE: merging multiple inherited definitions of column "tomorrow" 693NOTICE: merging column "tomorrow" with inherited definition 694DROP TABLE firstparent, secondparent, jointchild, thirdparent, otherchild; 695-- Test changing the type of inherited columns 696insert into d values('test','one','two','three'); 697alter table a alter column aa type integer using bit_length(aa); 698select * from d; 699 aa | bb | cc | dd 700----+-----+-----+------- 701 32 | one | two | three 702(1 row) 703 704-- The above verified that we can change the type of a multiply-inherited 705-- column; but we should reject that if any definition was inherited from 706-- an unrelated parent. 707create temp table parent1(f1 int, f2 int); 708create temp table parent2(f1 int, f3 bigint); 709create temp table childtab(f4 int) inherits(parent1, parent2); 710NOTICE: merging multiple inherited definitions of column "f1" 711alter table parent1 alter column f1 type bigint; -- fail, conflict w/parent2 712ERROR: cannot alter inherited column "f1" of relation "childtab" 713alter table parent1 alter column f2 type bigint; -- ok 714-- check that oid column is handled properly during alter table inherit 715create table oid_parent (a int) with oids; 716create table oid_child () inherits (oid_parent); 717select attinhcount, attislocal from pg_attribute 718 where attrelid = 'oid_child'::regclass and attname = 'oid'; 719 attinhcount | attislocal 720-------------+------------ 721 1 | f 722(1 row) 723 724drop table oid_child; 725create table oid_child (a int) without oids; 726alter table oid_child inherit oid_parent; -- fail 727ERROR: table "oid_child" without OIDs cannot inherit from table "oid_parent" with OIDs 728alter table oid_child set with oids; 729select attinhcount, attislocal from pg_attribute 730 where attrelid = 'oid_child'::regclass and attname = 'oid'; 731 attinhcount | attislocal 732-------------+------------ 733 0 | t 734(1 row) 735 736alter table oid_child inherit oid_parent; 737select attinhcount, attislocal from pg_attribute 738 where attrelid = 'oid_child'::regclass and attname = 'oid'; 739 attinhcount | attislocal 740-------------+------------ 741 1 | t 742(1 row) 743 744alter table oid_child set without oids; -- fail 745ERROR: cannot drop inherited column "oid" 746alter table oid_parent set without oids; 747select attinhcount, attislocal from pg_attribute 748 where attrelid = 'oid_child'::regclass and attname = 'oid'; 749 attinhcount | attislocal 750-------------+------------ 751 0 | t 752(1 row) 753 754alter table oid_child set without oids; 755select attinhcount, attislocal from pg_attribute 756 where attrelid = 'oid_child'::regclass and attname = 'oid'; 757 attinhcount | attislocal 758-------------+------------ 759(0 rows) 760 761drop table oid_parent cascade; 762NOTICE: drop cascades to table oid_child 763-- Test non-inheritable parent constraints 764create table p1(ff1 int); 765alter table p1 add constraint p1chk check (ff1 > 0) no inherit; 766alter table p1 add constraint p2chk check (ff1 > 10); 767-- connoinherit should be true for NO INHERIT constraint 768select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.connoinherit from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname = 'p1' order by 1,2; 769 relname | conname | contype | conislocal | coninhcount | connoinherit 770---------+---------+---------+------------+-------------+-------------- 771 p1 | p1chk | c | t | 0 | t 772 p1 | p2chk | c | t | 0 | f 773(2 rows) 774 775-- Test that child does not inherit NO INHERIT constraints 776create table c1 () inherits (p1); 777\d p1 778 Table "public.p1" 779 Column | Type | Collation | Nullable | Default 780--------+---------+-----------+----------+--------- 781 ff1 | integer | | | 782Check constraints: 783 "p1chk" CHECK (ff1 > 0) NO INHERIT 784 "p2chk" CHECK (ff1 > 10) 785Number of child tables: 1 (Use \d+ to list them.) 786 787\d c1 788 Table "public.c1" 789 Column | Type | Collation | Nullable | Default 790--------+---------+-----------+----------+--------- 791 ff1 | integer | | | 792Check constraints: 793 "p2chk" CHECK (ff1 > 10) 794Inherits: p1 795 796-- Test that child does not override inheritable constraints of the parent 797create table c2 (constraint p2chk check (ff1 > 10) no inherit) inherits (p1); --fails 798ERROR: constraint "p2chk" conflicts with inherited constraint on relation "c2" 799drop table p1 cascade; 800NOTICE: drop cascades to table c1 801-- Tests for casting between the rowtypes of parent and child 802-- tables. See the pgsql-hackers thread beginning Dec. 4/04 803create table base (i integer); 804create table derived () inherits (base); 805insert into derived (i) values (0); 806select derived::base from derived; 807 derived 808--------- 809 (0) 810(1 row) 811 812select NULL::derived::base; 813 base 814------ 815 816(1 row) 817 818drop table derived; 819drop table base; 820create table p1(ff1 int); 821create table p2(f1 text); 822create function p2text(p2) returns text as 'select $1.f1' language sql; 823create table c1(f3 int) inherits(p1,p2); 824insert into c1 values(123456789, 'hi', 42); 825select p2text(c1.*) from c1; 826 p2text 827-------- 828 hi 829(1 row) 830 831drop function p2text(p2); 832drop table c1; 833drop table p2; 834drop table p1; 835CREATE TABLE ac (aa TEXT); 836alter table ac add constraint ac_check check (aa is not null); 837CREATE TABLE bc (bb TEXT) INHERITS (ac); 838select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; 839 relname | conname | contype | conislocal | coninhcount | consrc 840---------+----------+---------+------------+-------------+------------------ 841 ac | ac_check | c | t | 0 | (aa IS NOT NULL) 842 bc | ac_check | c | f | 1 | (aa IS NOT NULL) 843(2 rows) 844 845insert into ac (aa) values (NULL); 846ERROR: new row for relation "ac" violates check constraint "ac_check" 847DETAIL: Failing row contains (null). 848insert into bc (aa) values (NULL); 849ERROR: new row for relation "bc" violates check constraint "ac_check" 850DETAIL: Failing row contains (null, null). 851alter table bc drop constraint ac_check; -- fail, disallowed 852ERROR: cannot drop inherited constraint "ac_check" of relation "bc" 853alter table ac drop constraint ac_check; 854select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; 855 relname | conname | contype | conislocal | coninhcount | consrc 856---------+---------+---------+------------+-------------+-------- 857(0 rows) 858 859-- try the unnamed-constraint case 860alter table ac add check (aa is not null); 861select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; 862 relname | conname | contype | conislocal | coninhcount | consrc 863---------+-------------+---------+------------+-------------+------------------ 864 ac | ac_aa_check | c | t | 0 | (aa IS NOT NULL) 865 bc | ac_aa_check | c | f | 1 | (aa IS NOT NULL) 866(2 rows) 867 868insert into ac (aa) values (NULL); 869ERROR: new row for relation "ac" violates check constraint "ac_aa_check" 870DETAIL: Failing row contains (null). 871insert into bc (aa) values (NULL); 872ERROR: new row for relation "bc" violates check constraint "ac_aa_check" 873DETAIL: Failing row contains (null, null). 874alter table bc drop constraint ac_aa_check; -- fail, disallowed 875ERROR: cannot drop inherited constraint "ac_aa_check" of relation "bc" 876alter table ac drop constraint ac_aa_check; 877select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; 878 relname | conname | contype | conislocal | coninhcount | consrc 879---------+---------+---------+------------+-------------+-------- 880(0 rows) 881 882alter table ac add constraint ac_check check (aa is not null); 883alter table bc no inherit ac; 884select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; 885 relname | conname | contype | conislocal | coninhcount | consrc 886---------+----------+---------+------------+-------------+------------------ 887 ac | ac_check | c | t | 0 | (aa IS NOT NULL) 888 bc | ac_check | c | t | 0 | (aa IS NOT NULL) 889(2 rows) 890 891alter table bc drop constraint ac_check; 892select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; 893 relname | conname | contype | conislocal | coninhcount | consrc 894---------+----------+---------+------------+-------------+------------------ 895 ac | ac_check | c | t | 0 | (aa IS NOT NULL) 896(1 row) 897 898alter table ac drop constraint ac_check; 899select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; 900 relname | conname | contype | conislocal | coninhcount | consrc 901---------+---------+---------+------------+-------------+-------- 902(0 rows) 903 904drop table bc; 905drop table ac; 906create table ac (a int constraint check_a check (a <> 0)); 907create table bc (a int constraint check_a check (a <> 0), b int constraint check_b check (b <> 0)) inherits (ac); 908NOTICE: merging column "a" with inherited definition 909NOTICE: merging constraint "check_a" with inherited definition 910select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc') order by 1,2; 911 relname | conname | contype | conislocal | coninhcount | consrc 912---------+---------+---------+------------+-------------+---------- 913 ac | check_a | c | t | 0 | (a <> 0) 914 bc | check_a | c | t | 1 | (a <> 0) 915 bc | check_b | c | t | 0 | (b <> 0) 916(3 rows) 917 918drop table bc; 919drop table ac; 920create table ac (a int constraint check_a check (a <> 0)); 921create table bc (b int constraint check_b check (b <> 0)); 922create table cc (c int constraint check_c check (c <> 0)) inherits (ac, bc); 923select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc', 'cc') order by 1,2; 924 relname | conname | contype | conislocal | coninhcount | consrc 925---------+---------+---------+------------+-------------+---------- 926 ac | check_a | c | t | 0 | (a <> 0) 927 bc | check_b | c | t | 0 | (b <> 0) 928 cc | check_a | c | f | 1 | (a <> 0) 929 cc | check_b | c | f | 1 | (b <> 0) 930 cc | check_c | c | t | 0 | (c <> 0) 931(5 rows) 932 933alter table cc no inherit bc; 934select pc.relname, pgc.conname, pgc.contype, pgc.conislocal, pgc.coninhcount, pgc.consrc from pg_class as pc inner join pg_constraint as pgc on (pgc.conrelid = pc.oid) where pc.relname in ('ac', 'bc', 'cc') order by 1,2; 935 relname | conname | contype | conislocal | coninhcount | consrc 936---------+---------+---------+------------+-------------+---------- 937 ac | check_a | c | t | 0 | (a <> 0) 938 bc | check_b | c | t | 0 | (b <> 0) 939 cc | check_a | c | f | 1 | (a <> 0) 940 cc | check_b | c | t | 0 | (b <> 0) 941 cc | check_c | c | t | 0 | (c <> 0) 942(5 rows) 943 944drop table cc; 945drop table bc; 946drop table ac; 947create table p1(f1 int); 948create table p2(f2 int); 949create table c1(f3 int) inherits(p1,p2); 950insert into c1 values(1,-1,2); 951alter table p2 add constraint cc check (f2>0); -- fail 952ERROR: check constraint "cc" is violated by some row 953alter table p2 add check (f2>0); -- check it without a name, too 954ERROR: check constraint "p2_f2_check" is violated by some row 955delete from c1; 956insert into c1 values(1,1,2); 957alter table p2 add check (f2>0); 958insert into c1 values(1,-1,2); -- fail 959ERROR: new row for relation "c1" violates check constraint "p2_f2_check" 960DETAIL: Failing row contains (1, -1, 2). 961create table c2(f3 int) inherits(p1,p2); 962\d c2 963 Table "public.c2" 964 Column | Type | Collation | Nullable | Default 965--------+---------+-----------+----------+--------- 966 f1 | integer | | | 967 f2 | integer | | | 968 f3 | integer | | | 969Check constraints: 970 "p2_f2_check" CHECK (f2 > 0) 971Inherits: p1, 972 p2 973 974create table c3 (f4 int) inherits(c1,c2); 975NOTICE: merging multiple inherited definitions of column "f1" 976NOTICE: merging multiple inherited definitions of column "f2" 977NOTICE: merging multiple inherited definitions of column "f3" 978\d c3 979 Table "public.c3" 980 Column | Type | Collation | Nullable | Default 981--------+---------+-----------+----------+--------- 982 f1 | integer | | | 983 f2 | integer | | | 984 f3 | integer | | | 985 f4 | integer | | | 986Check constraints: 987 "p2_f2_check" CHECK (f2 > 0) 988Inherits: c1, 989 c2 990 991drop table p1 cascade; 992NOTICE: drop cascades to 3 other objects 993DETAIL: drop cascades to table c1 994drop cascades to table c2 995drop cascades to table c3 996drop table p2 cascade; 997create table pp1 (f1 int); 998create table cc1 (f2 text, f3 int) inherits (pp1); 999alter table pp1 add column a1 int check (a1 > 0); 1000\d cc1 1001 Table "public.cc1" 1002 Column | Type | Collation | Nullable | Default 1003--------+---------+-----------+----------+--------- 1004 f1 | integer | | | 1005 f2 | text | | | 1006 f3 | integer | | | 1007 a1 | integer | | | 1008Check constraints: 1009 "pp1_a1_check" CHECK (a1 > 0) 1010Inherits: pp1 1011 1012create table cc2(f4 float) inherits(pp1,cc1); 1013NOTICE: merging multiple inherited definitions of column "f1" 1014NOTICE: merging multiple inherited definitions of column "a1" 1015\d cc2 1016 Table "public.cc2" 1017 Column | Type | Collation | Nullable | Default 1018--------+------------------+-----------+----------+--------- 1019 f1 | integer | | | 1020 a1 | integer | | | 1021 f2 | text | | | 1022 f3 | integer | | | 1023 f4 | double precision | | | 1024Check constraints: 1025 "pp1_a1_check" CHECK (a1 > 0) 1026Inherits: pp1, 1027 cc1 1028 1029alter table pp1 add column a2 int check (a2 > 0); 1030NOTICE: merging definition of column "a2" for child "cc2" 1031NOTICE: merging constraint "pp1_a2_check" with inherited definition 1032\d cc2 1033 Table "public.cc2" 1034 Column | Type | Collation | Nullable | Default 1035--------+------------------+-----------+----------+--------- 1036 f1 | integer | | | 1037 a1 | integer | | | 1038 f2 | text | | | 1039 f3 | integer | | | 1040 f4 | double precision | | | 1041 a2 | integer | | | 1042Check constraints: 1043 "pp1_a1_check" CHECK (a1 > 0) 1044 "pp1_a2_check" CHECK (a2 > 0) 1045Inherits: pp1, 1046 cc1 1047 1048drop table pp1 cascade; 1049NOTICE: drop cascades to 2 other objects 1050DETAIL: drop cascades to table cc1 1051drop cascades to table cc2 1052-- Test for renaming in simple multiple inheritance 1053CREATE TABLE inht1 (a int, b int); 1054CREATE TABLE inhs1 (b int, c int); 1055CREATE TABLE inhts (d int) INHERITS (inht1, inhs1); 1056NOTICE: merging multiple inherited definitions of column "b" 1057ALTER TABLE inht1 RENAME a TO aa; 1058ALTER TABLE inht1 RENAME b TO bb; -- to be failed 1059ERROR: cannot rename inherited column "b" 1060ALTER TABLE inhts RENAME aa TO aaa; -- to be failed 1061ERROR: cannot rename inherited column "aa" 1062ALTER TABLE inhts RENAME d TO dd; 1063\d+ inhts 1064 Table "public.inhts" 1065 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 1066--------+---------+-----------+----------+---------+---------+--------------+------------- 1067 aa | integer | | | | plain | | 1068 b | integer | | | | plain | | 1069 c | integer | | | | plain | | 1070 dd | integer | | | | plain | | 1071Inherits: inht1, 1072 inhs1 1073 1074DROP TABLE inhts; 1075-- Test for renaming in diamond inheritance 1076CREATE TABLE inht2 (x int) INHERITS (inht1); 1077CREATE TABLE inht3 (y int) INHERITS (inht1); 1078CREATE TABLE inht4 (z int) INHERITS (inht2, inht3); 1079NOTICE: merging multiple inherited definitions of column "aa" 1080NOTICE: merging multiple inherited definitions of column "b" 1081ALTER TABLE inht1 RENAME aa TO aaa; 1082\d+ inht4 1083 Table "public.inht4" 1084 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 1085--------+---------+-----------+----------+---------+---------+--------------+------------- 1086 aaa | integer | | | | plain | | 1087 b | integer | | | | plain | | 1088 x | integer | | | | plain | | 1089 y | integer | | | | plain | | 1090 z | integer | | | | plain | | 1091Inherits: inht2, 1092 inht3 1093 1094CREATE TABLE inhts (d int) INHERITS (inht2, inhs1); 1095NOTICE: merging multiple inherited definitions of column "b" 1096ALTER TABLE inht1 RENAME aaa TO aaaa; 1097ALTER TABLE inht1 RENAME b TO bb; -- to be failed 1098ERROR: cannot rename inherited column "b" 1099\d+ inhts 1100 Table "public.inhts" 1101 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 1102--------+---------+-----------+----------+---------+---------+--------------+------------- 1103 aaaa | integer | | | | plain | | 1104 b | integer | | | | plain | | 1105 x | integer | | | | plain | | 1106 c | integer | | | | plain | | 1107 d | integer | | | | plain | | 1108Inherits: inht2, 1109 inhs1 1110 1111WITH RECURSIVE r AS ( 1112 SELECT 'inht1'::regclass AS inhrelid 1113UNION ALL 1114 SELECT c.inhrelid FROM pg_inherits c, r WHERE r.inhrelid = c.inhparent 1115) 1116SELECT a.attrelid::regclass, a.attname, a.attinhcount, e.expected 1117 FROM (SELECT inhrelid, count(*) AS expected FROM pg_inherits 1118 WHERE inhparent IN (SELECT inhrelid FROM r) GROUP BY inhrelid) e 1119 JOIN pg_attribute a ON e.inhrelid = a.attrelid WHERE NOT attislocal 1120 ORDER BY a.attrelid::regclass::name, a.attnum; 1121 attrelid | attname | attinhcount | expected 1122----------+---------+-------------+---------- 1123 inht2 | aaaa | 1 | 1 1124 inht2 | b | 1 | 1 1125 inht3 | aaaa | 1 | 1 1126 inht3 | b | 1 | 1 1127 inht4 | aaaa | 2 | 2 1128 inht4 | b | 2 | 2 1129 inht4 | x | 1 | 2 1130 inht4 | y | 1 | 2 1131 inhts | aaaa | 1 | 1 1132 inhts | b | 2 | 1 1133 inhts | x | 1 | 1 1134 inhts | c | 1 | 1 1135(12 rows) 1136 1137DROP TABLE inht1, inhs1 CASCADE; 1138NOTICE: drop cascades to 4 other objects 1139DETAIL: drop cascades to table inht2 1140drop cascades to table inhts 1141drop cascades to table inht3 1142drop cascades to table inht4 1143-- Test non-inheritable indices [UNIQUE, EXCLUDE] constraints 1144CREATE TABLE test_constraints (id int, val1 varchar, val2 int, UNIQUE(val1, val2)); 1145CREATE TABLE test_constraints_inh () INHERITS (test_constraints); 1146\d+ test_constraints 1147 Table "public.test_constraints" 1148 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 1149--------+-------------------+-----------+----------+---------+----------+--------------+------------- 1150 id | integer | | | | plain | | 1151 val1 | character varying | | | | extended | | 1152 val2 | integer | | | | plain | | 1153Indexes: 1154 "test_constraints_val1_val2_key" UNIQUE CONSTRAINT, btree (val1, val2) 1155Child tables: test_constraints_inh 1156 1157ALTER TABLE ONLY test_constraints DROP CONSTRAINT test_constraints_val1_val2_key; 1158\d+ test_constraints 1159 Table "public.test_constraints" 1160 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 1161--------+-------------------+-----------+----------+---------+----------+--------------+------------- 1162 id | integer | | | | plain | | 1163 val1 | character varying | | | | extended | | 1164 val2 | integer | | | | plain | | 1165Child tables: test_constraints_inh 1166 1167\d+ test_constraints_inh 1168 Table "public.test_constraints_inh" 1169 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 1170--------+-------------------+-----------+----------+---------+----------+--------------+------------- 1171 id | integer | | | | plain | | 1172 val1 | character varying | | | | extended | | 1173 val2 | integer | | | | plain | | 1174Inherits: test_constraints 1175 1176DROP TABLE test_constraints_inh; 1177DROP TABLE test_constraints; 1178CREATE TABLE test_ex_constraints ( 1179 c circle, 1180 EXCLUDE USING gist (c WITH &&) 1181); 1182CREATE TABLE test_ex_constraints_inh () INHERITS (test_ex_constraints); 1183\d+ test_ex_constraints 1184 Table "public.test_ex_constraints" 1185 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 1186--------+--------+-----------+----------+---------+---------+--------------+------------- 1187 c | circle | | | | plain | | 1188Indexes: 1189 "test_ex_constraints_c_excl" EXCLUDE USING gist (c WITH &&) 1190Child tables: test_ex_constraints_inh 1191 1192ALTER TABLE test_ex_constraints DROP CONSTRAINT test_ex_constraints_c_excl; 1193\d+ test_ex_constraints 1194 Table "public.test_ex_constraints" 1195 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 1196--------+--------+-----------+----------+---------+---------+--------------+------------- 1197 c | circle | | | | plain | | 1198Child tables: test_ex_constraints_inh 1199 1200\d+ test_ex_constraints_inh 1201 Table "public.test_ex_constraints_inh" 1202 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 1203--------+--------+-----------+----------+---------+---------+--------------+------------- 1204 c | circle | | | | plain | | 1205Inherits: test_ex_constraints 1206 1207DROP TABLE test_ex_constraints_inh; 1208DROP TABLE test_ex_constraints; 1209-- Test non-inheritable foreign key constraints 1210CREATE TABLE test_primary_constraints(id int PRIMARY KEY); 1211CREATE TABLE test_foreign_constraints(id1 int REFERENCES test_primary_constraints(id)); 1212CREATE TABLE test_foreign_constraints_inh () INHERITS (test_foreign_constraints); 1213\d+ test_primary_constraints 1214 Table "public.test_primary_constraints" 1215 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 1216--------+---------+-----------+----------+---------+---------+--------------+------------- 1217 id | integer | | not null | | plain | | 1218Indexes: 1219 "test_primary_constraints_pkey" PRIMARY KEY, btree (id) 1220Referenced by: 1221 TABLE "test_foreign_constraints" CONSTRAINT "test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id) 1222 1223\d+ test_foreign_constraints 1224 Table "public.test_foreign_constraints" 1225 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 1226--------+---------+-----------+----------+---------+---------+--------------+------------- 1227 id1 | integer | | | | plain | | 1228Foreign-key constraints: 1229 "test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id) 1230Child tables: test_foreign_constraints_inh 1231 1232ALTER TABLE test_foreign_constraints DROP CONSTRAINT test_foreign_constraints_id1_fkey; 1233\d+ test_foreign_constraints 1234 Table "public.test_foreign_constraints" 1235 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 1236--------+---------+-----------+----------+---------+---------+--------------+------------- 1237 id1 | integer | | | | plain | | 1238Child tables: test_foreign_constraints_inh 1239 1240\d+ test_foreign_constraints_inh 1241 Table "public.test_foreign_constraints_inh" 1242 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 1243--------+---------+-----------+----------+---------+---------+--------------+------------- 1244 id1 | integer | | | | plain | | 1245Inherits: test_foreign_constraints 1246 1247DROP TABLE test_foreign_constraints_inh; 1248DROP TABLE test_foreign_constraints; 1249DROP TABLE test_primary_constraints; 1250-- Test that parent and child CHECK constraints can be created in either order 1251create table p1(f1 int); 1252create table p1_c1() inherits(p1); 1253alter table p1 add constraint inh_check_constraint1 check (f1 > 0); 1254alter table p1_c1 add constraint inh_check_constraint1 check (f1 > 0); 1255NOTICE: merging constraint "inh_check_constraint1" with inherited definition 1256alter table p1_c1 add constraint inh_check_constraint2 check (f1 < 10); 1257alter table p1 add constraint inh_check_constraint2 check (f1 < 10); 1258NOTICE: merging constraint "inh_check_constraint2" with inherited definition 1259select conrelid::regclass::text as relname, conname, conislocal, coninhcount 1260from pg_constraint where conname like 'inh\_check\_constraint%' 1261order by 1, 2; 1262 relname | conname | conislocal | coninhcount 1263---------+-----------------------+------------+------------- 1264 p1 | inh_check_constraint1 | t | 0 1265 p1 | inh_check_constraint2 | t | 0 1266 p1_c1 | inh_check_constraint1 | t | 1 1267 p1_c1 | inh_check_constraint2 | t | 1 1268(4 rows) 1269 1270drop table p1 cascade; 1271NOTICE: drop cascades to table p1_c1 1272-- Test that a valid child can have not-valid parent, but not vice versa 1273create table invalid_check_con(f1 int); 1274create table invalid_check_con_child() inherits(invalid_check_con); 1275alter table invalid_check_con_child add constraint inh_check_constraint check(f1 > 0) not valid; 1276alter table invalid_check_con add constraint inh_check_constraint check(f1 > 0); -- fail 1277ERROR: constraint "inh_check_constraint" conflicts with NOT VALID constraint on relation "invalid_check_con_child" 1278alter table invalid_check_con_child drop constraint inh_check_constraint; 1279insert into invalid_check_con values(0); 1280alter table invalid_check_con_child add constraint inh_check_constraint check(f1 > 0); 1281alter table invalid_check_con add constraint inh_check_constraint check(f1 > 0) not valid; 1282NOTICE: merging constraint "inh_check_constraint" with inherited definition 1283insert into invalid_check_con values(0); -- fail 1284ERROR: new row for relation "invalid_check_con" violates check constraint "inh_check_constraint" 1285DETAIL: Failing row contains (0). 1286insert into invalid_check_con_child values(0); -- fail 1287ERROR: new row for relation "invalid_check_con_child" violates check constraint "inh_check_constraint" 1288DETAIL: Failing row contains (0). 1289select conrelid::regclass::text as relname, conname, 1290 convalidated, conislocal, coninhcount, connoinherit 1291from pg_constraint where conname like 'inh\_check\_constraint%' 1292order by 1, 2; 1293 relname | conname | convalidated | conislocal | coninhcount | connoinherit 1294-------------------------+----------------------+--------------+------------+-------------+-------------- 1295 invalid_check_con | inh_check_constraint | f | t | 0 | f 1296 invalid_check_con_child | inh_check_constraint | t | t | 1 | f 1297(2 rows) 1298 1299-- We don't drop the invalid_check_con* tables, to test dump/reload with 1300-- 1301-- Test parameterized append plans for inheritance trees 1302-- 1303create temp table patest0 (id, x) as 1304 select x, x from generate_series(0,1000) x; 1305create temp table patest1() inherits (patest0); 1306insert into patest1 1307 select x, x from generate_series(0,1000) x; 1308create temp table patest2() inherits (patest0); 1309insert into patest2 1310 select x, x from generate_series(0,1000) x; 1311create index patest0i on patest0(id); 1312create index patest1i on patest1(id); 1313create index patest2i on patest2(id); 1314analyze patest0; 1315analyze patest1; 1316analyze patest2; 1317explain (costs off) 1318select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; 1319 QUERY PLAN 1320-------------------------------------------------- 1321 Nested Loop 1322 -> Limit 1323 -> Seq Scan on int4_tbl 1324 -> Append 1325 -> Index Scan using patest0i on patest0 1326 Index Cond: (id = int4_tbl.f1) 1327 -> Index Scan using patest1i on patest1 1328 Index Cond: (id = int4_tbl.f1) 1329 -> Index Scan using patest2i on patest2 1330 Index Cond: (id = int4_tbl.f1) 1331(10 rows) 1332 1333select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; 1334 id | x | f1 1335----+---+---- 1336 0 | 0 | 0 1337 0 | 0 | 0 1338 0 | 0 | 0 1339(3 rows) 1340 1341drop index patest2i; 1342explain (costs off) 1343select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; 1344 QUERY PLAN 1345-------------------------------------------------- 1346 Nested Loop 1347 -> Limit 1348 -> Seq Scan on int4_tbl 1349 -> Append 1350 -> Index Scan using patest0i on patest0 1351 Index Cond: (id = int4_tbl.f1) 1352 -> Index Scan using patest1i on patest1 1353 Index Cond: (id = int4_tbl.f1) 1354 -> Seq Scan on patest2 1355 Filter: (int4_tbl.f1 = id) 1356(10 rows) 1357 1358select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1; 1359 id | x | f1 1360----+---+---- 1361 0 | 0 | 0 1362 0 | 0 | 0 1363 0 | 0 | 0 1364(3 rows) 1365 1366drop table patest0 cascade; 1367NOTICE: drop cascades to 2 other objects 1368DETAIL: drop cascades to table patest1 1369drop cascades to table patest2 1370-- 1371-- Test merge-append plans for inheritance trees 1372-- 1373create table matest0 (id serial primary key, name text); 1374create table matest1 (id integer primary key) inherits (matest0); 1375NOTICE: merging column "id" with inherited definition 1376create table matest2 (id integer primary key) inherits (matest0); 1377NOTICE: merging column "id" with inherited definition 1378create table matest3 (id integer primary key) inherits (matest0); 1379NOTICE: merging column "id" with inherited definition 1380create index matest0i on matest0 ((1-id)); 1381create index matest1i on matest1 ((1-id)); 1382-- create index matest2i on matest2 ((1-id)); -- intentionally missing 1383create index matest3i on matest3 ((1-id)); 1384insert into matest1 (name) values ('Test 1'); 1385insert into matest1 (name) values ('Test 2'); 1386insert into matest2 (name) values ('Test 3'); 1387insert into matest2 (name) values ('Test 4'); 1388insert into matest3 (name) values ('Test 5'); 1389insert into matest3 (name) values ('Test 6'); 1390set enable_indexscan = off; -- force use of seqscan/sort, so no merge 1391explain (verbose, costs off) select * from matest0 order by 1-id; 1392 QUERY PLAN 1393------------------------------------------------------------ 1394 Sort 1395 Output: matest0.id, matest0.name, ((1 - matest0.id)) 1396 Sort Key: ((1 - matest0.id)) 1397 -> Result 1398 Output: matest0.id, matest0.name, (1 - matest0.id) 1399 -> Append 1400 -> Seq Scan on public.matest0 1401 Output: matest0.id, matest0.name 1402 -> Seq Scan on public.matest1 1403 Output: matest1.id, matest1.name 1404 -> Seq Scan on public.matest2 1405 Output: matest2.id, matest2.name 1406 -> Seq Scan on public.matest3 1407 Output: matest3.id, matest3.name 1408(14 rows) 1409 1410select * from matest0 order by 1-id; 1411 id | name 1412----+-------- 1413 6 | Test 6 1414 5 | Test 5 1415 4 | Test 4 1416 3 | Test 3 1417 2 | Test 2 1418 1 | Test 1 1419(6 rows) 1420 1421explain (verbose, costs off) select min(1-id) from matest0; 1422 QUERY PLAN 1423---------------------------------------- 1424 Aggregate 1425 Output: min((1 - matest0.id)) 1426 -> Append 1427 -> Seq Scan on public.matest0 1428 Output: matest0.id 1429 -> Seq Scan on public.matest1 1430 Output: matest1.id 1431 -> Seq Scan on public.matest2 1432 Output: matest2.id 1433 -> Seq Scan on public.matest3 1434 Output: matest3.id 1435(11 rows) 1436 1437select min(1-id) from matest0; 1438 min 1439----- 1440 -5 1441(1 row) 1442 1443reset enable_indexscan; 1444set enable_seqscan = off; -- plan with fewest seqscans should be merge 1445explain (verbose, costs off) select * from matest0 order by 1-id; 1446 QUERY PLAN 1447------------------------------------------------------------------ 1448 Merge Append 1449 Sort Key: ((1 - matest0.id)) 1450 -> Index Scan using matest0i on public.matest0 1451 Output: matest0.id, matest0.name, (1 - matest0.id) 1452 -> Index Scan using matest1i on public.matest1 1453 Output: matest1.id, matest1.name, (1 - matest1.id) 1454 -> Sort 1455 Output: matest2.id, matest2.name, ((1 - matest2.id)) 1456 Sort Key: ((1 - matest2.id)) 1457 -> Seq Scan on public.matest2 1458 Output: matest2.id, matest2.name, (1 - matest2.id) 1459 -> Index Scan using matest3i on public.matest3 1460 Output: matest3.id, matest3.name, (1 - matest3.id) 1461(13 rows) 1462 1463select * from matest0 order by 1-id; 1464 id | name 1465----+-------- 1466 6 | Test 6 1467 5 | Test 5 1468 4 | Test 4 1469 3 | Test 3 1470 2 | Test 2 1471 1 | Test 1 1472(6 rows) 1473 1474explain (verbose, costs off) select min(1-id) from matest0; 1475 QUERY PLAN 1476-------------------------------------------------------------------------- 1477 Result 1478 Output: $0 1479 InitPlan 1 (returns $0) 1480 -> Limit 1481 Output: ((1 - matest0.id)) 1482 -> Result 1483 Output: ((1 - matest0.id)) 1484 -> Merge Append 1485 Sort Key: ((1 - matest0.id)) 1486 -> Index Scan using matest0i on public.matest0 1487 Output: matest0.id, (1 - matest0.id) 1488 Index Cond: ((1 - matest0.id) IS NOT NULL) 1489 -> Index Scan using matest1i on public.matest1 1490 Output: matest1.id, (1 - matest1.id) 1491 Index Cond: ((1 - matest1.id) IS NOT NULL) 1492 -> Sort 1493 Output: matest2.id, ((1 - matest2.id)) 1494 Sort Key: ((1 - matest2.id)) 1495 -> Bitmap Heap Scan on public.matest2 1496 Output: matest2.id, (1 - matest2.id) 1497 Filter: ((1 - matest2.id) IS NOT NULL) 1498 -> Bitmap Index Scan on matest2_pkey 1499 -> Index Scan using matest3i on public.matest3 1500 Output: matest3.id, (1 - matest3.id) 1501 Index Cond: ((1 - matest3.id) IS NOT NULL) 1502(25 rows) 1503 1504select min(1-id) from matest0; 1505 min 1506----- 1507 -5 1508(1 row) 1509 1510reset enable_seqscan; 1511drop table matest0 cascade; 1512NOTICE: drop cascades to 3 other objects 1513DETAIL: drop cascades to table matest1 1514drop cascades to table matest2 1515drop cascades to table matest3 1516-- 1517-- Check that use of an index with an extraneous column doesn't produce 1518-- a plan with extraneous sorting 1519-- 1520create table matest0 (a int, b int, c int, d int); 1521create table matest1 () inherits(matest0); 1522create index matest0i on matest0 (b, c); 1523create index matest1i on matest1 (b, c); 1524set enable_nestloop = off; -- we want a plan with two MergeAppends 1525explain (costs off) 1526select t1.* from matest0 t1, matest0 t2 1527where t1.b = t2.b and t2.c = t2.d 1528order by t1.b limit 10; 1529 QUERY PLAN 1530------------------------------------------------------------------- 1531 Limit 1532 -> Merge Join 1533 Merge Cond: (t1.b = t2.b) 1534 -> Merge Append 1535 Sort Key: t1.b 1536 -> Index Scan using matest0i on matest0 t1 1537 -> Index Scan using matest1i on matest1 t1_1 1538 -> Materialize 1539 -> Merge Append 1540 Sort Key: t2.b 1541 -> Index Scan using matest0i on matest0 t2 1542 Filter: (c = d) 1543 -> Index Scan using matest1i on matest1 t2_1 1544 Filter: (c = d) 1545(14 rows) 1546 1547reset enable_nestloop; 1548drop table matest0 cascade; 1549NOTICE: drop cascades to table matest1 1550-- 1551-- Test merge-append for UNION ALL append relations 1552-- 1553set enable_seqscan = off; 1554set enable_indexscan = on; 1555set enable_bitmapscan = off; 1556-- Check handling of duplicated, constant, or volatile targetlist items 1557explain (costs off) 1558SELECT thousand, tenthous FROM tenk1 1559UNION ALL 1560SELECT thousand, thousand FROM tenk1 1561ORDER BY thousand, tenthous; 1562 QUERY PLAN 1563------------------------------------------------------------------------- 1564 Merge Append 1565 Sort Key: tenk1.thousand, tenk1.tenthous 1566 -> Index Only Scan using tenk1_thous_tenthous on tenk1 1567 -> Sort 1568 Sort Key: tenk1_1.thousand, tenk1_1.thousand 1569 -> Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1 1570(6 rows) 1571 1572explain (costs off) 1573SELECT thousand, tenthous, thousand+tenthous AS x FROM tenk1 1574UNION ALL 1575SELECT 42, 42, hundred FROM tenk1 1576ORDER BY thousand, tenthous; 1577 QUERY PLAN 1578------------------------------------------------------------------ 1579 Merge Append 1580 Sort Key: tenk1.thousand, tenk1.tenthous 1581 -> Index Only Scan using tenk1_thous_tenthous on tenk1 1582 -> Sort 1583 Sort Key: 42, 42 1584 -> Index Only Scan using tenk1_hundred on tenk1 tenk1_1 1585(6 rows) 1586 1587explain (costs off) 1588SELECT thousand, tenthous FROM tenk1 1589UNION ALL 1590SELECT thousand, random()::integer FROM tenk1 1591ORDER BY thousand, tenthous; 1592 QUERY PLAN 1593------------------------------------------------------------------------- 1594 Merge Append 1595 Sort Key: tenk1.thousand, tenk1.tenthous 1596 -> Index Only Scan using tenk1_thous_tenthous on tenk1 1597 -> Sort 1598 Sort Key: tenk1_1.thousand, ((random())::integer) 1599 -> Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1 1600(6 rows) 1601 1602-- Check min/max aggregate optimization 1603explain (costs off) 1604SELECT min(x) FROM 1605 (SELECT unique1 AS x FROM tenk1 a 1606 UNION ALL 1607 SELECT unique2 AS x FROM tenk1 b) s; 1608 QUERY PLAN 1609-------------------------------------------------------------------- 1610 Result 1611 InitPlan 1 (returns $0) 1612 -> Limit 1613 -> Merge Append 1614 Sort Key: a.unique1 1615 -> Index Only Scan using tenk1_unique1 on tenk1 a 1616 Index Cond: (unique1 IS NOT NULL) 1617 -> Index Only Scan using tenk1_unique2 on tenk1 b 1618 Index Cond: (unique2 IS NOT NULL) 1619(9 rows) 1620 1621explain (costs off) 1622SELECT min(y) FROM 1623 (SELECT unique1 AS x, unique1 AS y FROM tenk1 a 1624 UNION ALL 1625 SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s; 1626 QUERY PLAN 1627-------------------------------------------------------------------- 1628 Result 1629 InitPlan 1 (returns $0) 1630 -> Limit 1631 -> Merge Append 1632 Sort Key: a.unique1 1633 -> Index Only Scan using tenk1_unique1 on tenk1 a 1634 Index Cond: (unique1 IS NOT NULL) 1635 -> Index Only Scan using tenk1_unique2 on tenk1 b 1636 Index Cond: (unique2 IS NOT NULL) 1637(9 rows) 1638 1639-- XXX planner doesn't recognize that index on unique2 is sufficiently sorted 1640explain (costs off) 1641SELECT x, y FROM 1642 (SELECT thousand AS x, tenthous AS y FROM tenk1 a 1643 UNION ALL 1644 SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s 1645ORDER BY x, y; 1646 QUERY PLAN 1647------------------------------------------------------------- 1648 Merge Append 1649 Sort Key: a.thousand, a.tenthous 1650 -> Index Only Scan using tenk1_thous_tenthous on tenk1 a 1651 -> Sort 1652 Sort Key: b.unique2, b.unique2 1653 -> Index Only Scan using tenk1_unique2 on tenk1 b 1654(6 rows) 1655 1656-- exercise rescan code path via a repeatedly-evaluated subquery 1657explain (costs off) 1658SELECT 1659 ARRAY(SELECT f.i FROM ( 1660 (SELECT d + g.i FROM generate_series(4, 30, 3) d ORDER BY 1) 1661 UNION ALL 1662 (SELECT d + g.i FROM generate_series(0, 30, 5) d ORDER BY 1) 1663 ) f(i) 1664 ORDER BY f.i LIMIT 10) 1665FROM generate_series(1, 3) g(i); 1666 QUERY PLAN 1667---------------------------------------------------------------- 1668 Function Scan on generate_series g 1669 SubPlan 1 1670 -> Limit 1671 -> Merge Append 1672 Sort Key: ((d.d + g.i)) 1673 -> Sort 1674 Sort Key: ((d.d + g.i)) 1675 -> Function Scan on generate_series d 1676 -> Sort 1677 Sort Key: ((d_1.d + g.i)) 1678 -> Function Scan on generate_series d_1 1679(11 rows) 1680 1681SELECT 1682 ARRAY(SELECT f.i FROM ( 1683 (SELECT d + g.i FROM generate_series(4, 30, 3) d ORDER BY 1) 1684 UNION ALL 1685 (SELECT d + g.i FROM generate_series(0, 30, 5) d ORDER BY 1) 1686 ) f(i) 1687 ORDER BY f.i LIMIT 10) 1688FROM generate_series(1, 3) g(i); 1689 array 1690------------------------------ 1691 {1,5,6,8,11,11,14,16,17,20} 1692 {2,6,7,9,12,12,15,17,18,21} 1693 {3,7,8,10,13,13,16,18,19,22} 1694(3 rows) 1695 1696reset enable_seqscan; 1697reset enable_indexscan; 1698reset enable_bitmapscan; 1699-- 1700-- Check handling of a constant-null CHECK constraint 1701-- 1702create table cnullparent (f1 int); 1703create table cnullchild (check (f1 = 1 or f1 = null)) inherits(cnullparent); 1704insert into cnullchild values(1); 1705insert into cnullchild values(2); 1706insert into cnullchild values(null); 1707select * from cnullparent; 1708 f1 1709---- 1710 1 1711 2 1712 1713(3 rows) 1714 1715select * from cnullparent where f1 = 2; 1716 f1 1717---- 1718 2 1719(1 row) 1720 1721drop table cnullparent cascade; 1722NOTICE: drop cascades to table cnullchild 1723-- 1724-- Check that constraint exclusion works correctly with partitions using 1725-- implicit constraints generated from the partition bound information. 1726-- 1727create table list_parted ( 1728 a varchar 1729) partition by list (a); 1730create table part_ab_cd partition of list_parted for values in ('ab', 'cd'); 1731create table part_ef_gh partition of list_parted for values in ('ef', 'gh'); 1732create table part_null_xy partition of list_parted for values in (null, 'xy'); 1733explain (costs off) select * from list_parted; 1734 QUERY PLAN 1735-------------------------------- 1736 Append 1737 -> Seq Scan on part_ab_cd 1738 -> Seq Scan on part_ef_gh 1739 -> Seq Scan on part_null_xy 1740(4 rows) 1741 1742explain (costs off) select * from list_parted where a is null; 1743 QUERY PLAN 1744-------------------------------- 1745 Append 1746 -> Seq Scan on part_null_xy 1747 Filter: (a IS NULL) 1748(3 rows) 1749 1750explain (costs off) select * from list_parted where a is not null; 1751 QUERY PLAN 1752--------------------------------- 1753 Append 1754 -> Seq Scan on part_ab_cd 1755 Filter: (a IS NOT NULL) 1756 -> Seq Scan on part_ef_gh 1757 Filter: (a IS NOT NULL) 1758 -> Seq Scan on part_null_xy 1759 Filter: (a IS NOT NULL) 1760(7 rows) 1761 1762explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef'); 1763 QUERY PLAN 1764---------------------------------------------------------- 1765 Append 1766 -> Seq Scan on part_ab_cd 1767 Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) 1768 -> Seq Scan on part_ef_gh 1769 Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) 1770(5 rows) 1771 1772explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd'); 1773 QUERY PLAN 1774--------------------------------------------------------------------------------------- 1775 Append 1776 -> Seq Scan on part_ab_cd 1777 Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) 1778 -> Seq Scan on part_ef_gh 1779 Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) 1780 -> Seq Scan on part_null_xy 1781 Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) 1782(7 rows) 1783 1784explain (costs off) select * from list_parted where a = 'ab'; 1785 QUERY PLAN 1786------------------------------------------ 1787 Append 1788 -> Seq Scan on part_ab_cd 1789 Filter: ((a)::text = 'ab'::text) 1790(3 rows) 1791 1792create table range_list_parted ( 1793 a int, 1794 b char(2) 1795) partition by range (a); 1796create table part_1_10 partition of range_list_parted for values from (1) to (10) partition by list (b); 1797create table part_1_10_ab partition of part_1_10 for values in ('ab'); 1798create table part_1_10_cd partition of part_1_10 for values in ('cd'); 1799create table part_10_20 partition of range_list_parted for values from (10) to (20) partition by list (b); 1800create table part_10_20_ab partition of part_10_20 for values in ('ab'); 1801create table part_10_20_cd partition of part_10_20 for values in ('cd'); 1802create table part_21_30 partition of range_list_parted for values from (21) to (30) partition by list (b); 1803create table part_21_30_ab partition of part_21_30 for values in ('ab'); 1804create table part_21_30_cd partition of part_21_30 for values in ('cd'); 1805create table part_40_inf partition of range_list_parted for values from (40) to (maxvalue) partition by list (b); 1806create table part_40_inf_ab partition of part_40_inf for values in ('ab'); 1807create table part_40_inf_cd partition of part_40_inf for values in ('cd'); 1808create table part_40_inf_null partition of part_40_inf for values in (null); 1809explain (costs off) select * from range_list_parted; 1810 QUERY PLAN 1811------------------------------------ 1812 Append 1813 -> Seq Scan on part_1_10_ab 1814 -> Seq Scan on part_1_10_cd 1815 -> Seq Scan on part_10_20_ab 1816 -> Seq Scan on part_10_20_cd 1817 -> Seq Scan on part_21_30_ab 1818 -> Seq Scan on part_21_30_cd 1819 -> Seq Scan on part_40_inf_ab 1820 -> Seq Scan on part_40_inf_cd 1821 -> Seq Scan on part_40_inf_null 1822(10 rows) 1823 1824explain (costs off) select * from range_list_parted where a = 5; 1825 QUERY PLAN 1826-------------------------------- 1827 Append 1828 -> Seq Scan on part_1_10_ab 1829 Filter: (a = 5) 1830 -> Seq Scan on part_1_10_cd 1831 Filter: (a = 5) 1832(5 rows) 1833 1834explain (costs off) select * from range_list_parted where b = 'ab'; 1835 QUERY PLAN 1836------------------------------------ 1837 Append 1838 -> Seq Scan on part_1_10_ab 1839 Filter: (b = 'ab'::bpchar) 1840 -> Seq Scan on part_10_20_ab 1841 Filter: (b = 'ab'::bpchar) 1842 -> Seq Scan on part_21_30_ab 1843 Filter: (b = 'ab'::bpchar) 1844 -> Seq Scan on part_40_inf_ab 1845 Filter: (b = 'ab'::bpchar) 1846(9 rows) 1847 1848explain (costs off) select * from range_list_parted where a between 3 and 23 and b in ('ab'); 1849 QUERY PLAN 1850----------------------------------------------------------------- 1851 Append 1852 -> Seq Scan on part_1_10_ab 1853 Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) 1854 -> Seq Scan on part_10_20_ab 1855 Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) 1856 -> Seq Scan on part_21_30_ab 1857 Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) 1858(7 rows) 1859 1860/* Should select no rows because range partition key cannot be null */ 1861explain (costs off) select * from range_list_parted where a is null; 1862 QUERY PLAN 1863-------------------------- 1864 Result 1865 One-Time Filter: false 1866(2 rows) 1867 1868/* Should only select rows from the null-accepting partition */ 1869explain (costs off) select * from range_list_parted where b is null; 1870 QUERY PLAN 1871------------------------------------ 1872 Append 1873 -> Seq Scan on part_40_inf_null 1874 Filter: (b IS NULL) 1875(3 rows) 1876 1877explain (costs off) select * from range_list_parted where a is not null and a < 67; 1878 QUERY PLAN 1879------------------------------------------------ 1880 Append 1881 -> Seq Scan on part_1_10_ab 1882 Filter: ((a IS NOT NULL) AND (a < 67)) 1883 -> Seq Scan on part_1_10_cd 1884 Filter: ((a IS NOT NULL) AND (a < 67)) 1885 -> Seq Scan on part_10_20_ab 1886 Filter: ((a IS NOT NULL) AND (a < 67)) 1887 -> Seq Scan on part_10_20_cd 1888 Filter: ((a IS NOT NULL) AND (a < 67)) 1889 -> Seq Scan on part_21_30_ab 1890 Filter: ((a IS NOT NULL) AND (a < 67)) 1891 -> Seq Scan on part_21_30_cd 1892 Filter: ((a IS NOT NULL) AND (a < 67)) 1893 -> Seq Scan on part_40_inf_ab 1894 Filter: ((a IS NOT NULL) AND (a < 67)) 1895 -> Seq Scan on part_40_inf_cd 1896 Filter: ((a IS NOT NULL) AND (a < 67)) 1897 -> Seq Scan on part_40_inf_null 1898 Filter: ((a IS NOT NULL) AND (a < 67)) 1899(19 rows) 1900 1901explain (costs off) select * from range_list_parted where a >= 30; 1902 QUERY PLAN 1903------------------------------------ 1904 Append 1905 -> Seq Scan on part_40_inf_ab 1906 Filter: (a >= 30) 1907 -> Seq Scan on part_40_inf_cd 1908 Filter: (a >= 30) 1909 -> Seq Scan on part_40_inf_null 1910 Filter: (a >= 30) 1911(7 rows) 1912 1913drop table list_parted; 1914drop table range_list_parted; 1915-- check that constraint exclusion is able to cope with the partition 1916-- constraint emitted for multi-column range partitioned tables 1917create table mcrparted (a int, b int, c int) partition by range (a, abs(b), c); 1918create table mcrparted0 partition of mcrparted for values from (minvalue, minvalue, minvalue) to (1, 1, 1); 1919create table mcrparted1 partition of mcrparted for values from (1, 1, 1) to (10, 5, 10); 1920create table mcrparted2 partition of mcrparted for values from (10, 5, 10) to (10, 10, 10); 1921create table mcrparted3 partition of mcrparted for values from (11, 1, 1) to (20, 10, 10); 1922create table mcrparted4 partition of mcrparted for values from (20, 10, 10) to (20, 20, 20); 1923create table mcrparted5 partition of mcrparted for values from (20, 20, 20) to (maxvalue, maxvalue, maxvalue); 1924explain (costs off) select * from mcrparted where a = 0; -- scans mcrparted0 1925 QUERY PLAN 1926------------------------------ 1927 Append 1928 -> Seq Scan on mcrparted0 1929 Filter: (a = 0) 1930(3 rows) 1931 1932explain (costs off) select * from mcrparted where a = 10 and abs(b) < 5; -- scans mcrparted1 1933 QUERY PLAN 1934--------------------------------------------- 1935 Append 1936 -> Seq Scan on mcrparted1 1937 Filter: ((a = 10) AND (abs(b) < 5)) 1938(3 rows) 1939 1940explain (costs off) select * from mcrparted where a = 10 and abs(b) = 5; -- scans mcrparted1, mcrparted2 1941 QUERY PLAN 1942--------------------------------------------- 1943 Append 1944 -> Seq Scan on mcrparted1 1945 Filter: ((a = 10) AND (abs(b) = 5)) 1946 -> Seq Scan on mcrparted2 1947 Filter: ((a = 10) AND (abs(b) = 5)) 1948(5 rows) 1949 1950explain (costs off) select * from mcrparted where abs(b) = 5; -- scans all partitions 1951 QUERY PLAN 1952------------------------------ 1953 Append 1954 -> Seq Scan on mcrparted0 1955 Filter: (abs(b) = 5) 1956 -> Seq Scan on mcrparted1 1957 Filter: (abs(b) = 5) 1958 -> Seq Scan on mcrparted2 1959 Filter: (abs(b) = 5) 1960 -> Seq Scan on mcrparted3 1961 Filter: (abs(b) = 5) 1962 -> Seq Scan on mcrparted5 1963 Filter: (abs(b) = 5) 1964(11 rows) 1965 1966explain (costs off) select * from mcrparted where a > -1; -- scans all partitions 1967 QUERY PLAN 1968------------------------------------- 1969 Append 1970 -> Seq Scan on mcrparted0 1971 Filter: (a > '-1'::integer) 1972 -> Seq Scan on mcrparted1 1973 Filter: (a > '-1'::integer) 1974 -> Seq Scan on mcrparted2 1975 Filter: (a > '-1'::integer) 1976 -> Seq Scan on mcrparted3 1977 Filter: (a > '-1'::integer) 1978 -> Seq Scan on mcrparted4 1979 Filter: (a > '-1'::integer) 1980 -> Seq Scan on mcrparted5 1981 Filter: (a > '-1'::integer) 1982(13 rows) 1983 1984explain (costs off) select * from mcrparted where a = 20 and abs(b) = 10 and c > 10; -- scans mcrparted4 1985 QUERY PLAN 1986----------------------------------------------------------- 1987 Append 1988 -> Seq Scan on mcrparted4 1989 Filter: ((c > 10) AND (a = 20) AND (abs(b) = 10)) 1990(3 rows) 1991 1992explain (costs off) select * from mcrparted where a = 20 and c > 20; -- scans mcrparted3, mcrparte4, mcrparte5 1993 QUERY PLAN 1994----------------------------------------- 1995 Append 1996 -> Seq Scan on mcrparted3 1997 Filter: ((c > 20) AND (a = 20)) 1998 -> Seq Scan on mcrparted4 1999 Filter: ((c > 20) AND (a = 20)) 2000 -> Seq Scan on mcrparted5 2001 Filter: ((c > 20) AND (a = 20)) 2002(7 rows) 2003 2004drop table mcrparted; 2005-- check that partitioned table Appends cope with being referenced in 2006-- subplans 2007create table parted_minmax (a int, b varchar(16)) partition by range (a); 2008create table parted_minmax1 partition of parted_minmax for values from (1) to (10); 2009create index parted_minmax1i on parted_minmax1 (a, b); 2010insert into parted_minmax values (1,'12345'); 2011explain (costs off) select min(a), max(a) from parted_minmax where b = '12345'; 2012 QUERY PLAN 2013------------------------------------------------------------------------------------------------------- 2014 Result 2015 InitPlan 1 (returns $0) 2016 -> Limit 2017 -> Merge Append 2018 Sort Key: parted_minmax1.a 2019 -> Index Only Scan using parted_minmax1i on parted_minmax1 2020 Index Cond: ((a IS NOT NULL) AND (b = '12345'::text)) 2021 InitPlan 2 (returns $1) 2022 -> Limit 2023 -> Merge Append 2024 Sort Key: parted_minmax1_1.a DESC 2025 -> Index Only Scan Backward using parted_minmax1i on parted_minmax1 parted_minmax1_1 2026 Index Cond: ((a IS NOT NULL) AND (b = '12345'::text)) 2027(13 rows) 2028 2029select min(a), max(a) from parted_minmax where b = '12345'; 2030 min | max 2031-----+----- 2032 1 | 1 2033(1 row) 2034 2035drop table parted_minmax; 2036-- 2037-- check that pruning works properly when the partition key is of a 2038-- pseudotype 2039-- 2040-- array type list partition key 2041create table pp_arrpart (a int[]) partition by list (a); 2042create table pp_arrpart1 partition of pp_arrpart for values in ('{1}'); 2043create table pp_arrpart2 partition of pp_arrpart for values in ('{2, 3}', '{4, 5}'); 2044explain (costs off) select * from pp_arrpart where a = '{1}'; 2045 QUERY PLAN 2046---------------------------------------- 2047 Append 2048 -> Seq Scan on pp_arrpart1 2049 Filter: (a = '{1}'::integer[]) 2050(3 rows) 2051 2052explain (costs off) select * from pp_arrpart where a = '{1, 2}'; 2053 QUERY PLAN 2054-------------------------- 2055 Result 2056 One-Time Filter: false 2057(2 rows) 2058 2059explain (costs off) select * from pp_arrpart where a in ('{4, 5}', '{1}'); 2060 QUERY PLAN 2061---------------------------------------------------------------------- 2062 Append 2063 -> Seq Scan on pp_arrpart1 2064 Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[])) 2065 -> Seq Scan on pp_arrpart2 2066 Filter: ((a = '{4,5}'::integer[]) OR (a = '{1}'::integer[])) 2067(5 rows) 2068 2069explain (costs off) update pp_arrpart set a = a where a = '{1}'; 2070 QUERY PLAN 2071---------------------------------------- 2072 Update on pp_arrpart 2073 Update on pp_arrpart1 2074 -> Seq Scan on pp_arrpart1 2075 Filter: (a = '{1}'::integer[]) 2076(4 rows) 2077 2078explain (costs off) delete from pp_arrpart where a = '{1}'; 2079 QUERY PLAN 2080---------------------------------------- 2081 Delete on pp_arrpart 2082 Delete on pp_arrpart1 2083 -> Seq Scan on pp_arrpart1 2084 Filter: (a = '{1}'::integer[]) 2085(4 rows) 2086 2087drop table pp_arrpart; 2088-- enum type list partition key 2089create type pp_colors as enum ('green', 'blue', 'black'); 2090create table pp_enumpart (a pp_colors) partition by list (a); 2091create table pp_enumpart_green partition of pp_enumpart for values in ('green'); 2092create table pp_enumpart_blue partition of pp_enumpart for values in ('blue'); 2093explain (costs off) select * from pp_enumpart where a = 'blue'; 2094 QUERY PLAN 2095----------------------------------------- 2096 Append 2097 -> Seq Scan on pp_enumpart_blue 2098 Filter: (a = 'blue'::pp_colors) 2099(3 rows) 2100 2101explain (costs off) select * from pp_enumpart where a = 'black'; 2102 QUERY PLAN 2103-------------------------- 2104 Result 2105 One-Time Filter: false 2106(2 rows) 2107 2108drop table pp_enumpart; 2109drop type pp_colors; 2110-- record type as partition key 2111create type pp_rectype as (a int, b int); 2112create table pp_recpart (a pp_rectype) partition by list (a); 2113create table pp_recpart_11 partition of pp_recpart for values in ('(1,1)'); 2114create table pp_recpart_23 partition of pp_recpart for values in ('(2,3)'); 2115explain (costs off) select * from pp_recpart where a = '(1,1)'::pp_rectype; 2116 QUERY PLAN 2117------------------------------------------- 2118 Append 2119 -> Seq Scan on pp_recpart_11 2120 Filter: (a = '(1,1)'::pp_rectype) 2121(3 rows) 2122 2123explain (costs off) select * from pp_recpart where a = '(1,2)'::pp_rectype; 2124 QUERY PLAN 2125-------------------------- 2126 Result 2127 One-Time Filter: false 2128(2 rows) 2129 2130drop table pp_recpart; 2131drop type pp_rectype; 2132-- range type partition key 2133create table pp_intrangepart (a int4range) partition by list (a); 2134create table pp_intrangepart12 partition of pp_intrangepart for values in ('[1,2]'); 2135create table pp_intrangepart2inf partition of pp_intrangepart for values in ('[2,)'); 2136explain (costs off) select * from pp_intrangepart where a = '[1,2]'::int4range; 2137 QUERY PLAN 2138------------------------------------------ 2139 Append 2140 -> Seq Scan on pp_intrangepart12 2141 Filter: (a = '[1,3)'::int4range) 2142(3 rows) 2143 2144explain (costs off) select * from pp_intrangepart where a = '(1,2)'::int4range; 2145 QUERY PLAN 2146-------------------------- 2147 Result 2148 One-Time Filter: false 2149(2 rows) 2150 2151drop table pp_intrangepart; 2152-- Verify that constraint errors across partition root / child are 2153-- handled correctly (Bug #16293) 2154CREATE TABLE errtst_parent ( 2155 partid int not null, 2156 shdata int not null, 2157 data int NOT NULL DEFAULT 0, 2158 CONSTRAINT shdata_small CHECK(shdata < 3) 2159) PARTITION BY RANGE (partid); 2160-- fast defaults lead to attribute mapping being used in one 2161-- direction, but not the other 2162CREATE TABLE errtst_child_fastdef ( 2163 partid int not null, 2164 shdata int not null, 2165 CONSTRAINT shdata_small CHECK(shdata < 3) 2166); 2167-- no remapping in either direction necessary 2168CREATE TABLE errtst_child_plaindef ( 2169 partid int not null, 2170 shdata int not null, 2171 data int NOT NULL DEFAULT 0, 2172 CONSTRAINT shdata_small CHECK(shdata < 3), 2173 CHECK(data < 10) 2174); 2175-- remapping in both direction 2176CREATE TABLE errtst_child_reorder ( 2177 data int NOT NULL DEFAULT 0, 2178 shdata int not null, 2179 partid int not null, 2180 CONSTRAINT shdata_small CHECK(shdata < 3), 2181 CHECK(data < 10) 2182); 2183ALTER TABLE errtst_child_fastdef ADD COLUMN data int NOT NULL DEFAULT 0; 2184ALTER TABLE errtst_child_fastdef ADD CONSTRAINT errtest_child_fastdef_data_check CHECK (data < 10); 2185ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_fastdef FOR VALUES FROM (0) TO (10); 2186ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_plaindef FOR VALUES FROM (10) TO (20); 2187ALTER TABLE errtst_parent ATTACH PARTITION errtst_child_reorder FOR VALUES FROM (20) TO (30); 2188-- insert without child check constraint error 2189INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', '5'); 2190INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', '5'); 2191INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', '5'); 2192-- insert with child check constraint error 2193INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', '10'); 2194ERROR: new row for relation "errtst_child_fastdef" violates check constraint "errtest_child_fastdef_data_check" 2195DETAIL: Failing row contains (0, 1, 10). 2196INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', '10'); 2197ERROR: new row for relation "errtst_child_plaindef" violates check constraint "errtst_child_plaindef_data_check" 2198DETAIL: Failing row contains (10, 1, 10). 2199INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', '10'); 2200ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check" 2201DETAIL: Failing row contains (20, 1, 10). 2202-- insert with child not null constraint error 2203INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '1', NULL); 2204ERROR: null value in column "data" violates not-null constraint 2205DETAIL: Failing row contains (0, 1, null). 2206INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '1', NULL); 2207ERROR: null value in column "data" violates not-null constraint 2208DETAIL: Failing row contains (10, 1, null). 2209INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '1', NULL); 2210ERROR: null value in column "data" violates not-null constraint 2211DETAIL: Failing row contains (20, 1, null). 2212-- insert with shared check constraint error 2213INSERT INTO errtst_parent(partid, shdata, data) VALUES ( '0', '5', '5'); 2214ERROR: new row for relation "errtst_child_fastdef" violates check constraint "shdata_small" 2215DETAIL: Failing row contains (0, 5, 5). 2216INSERT INTO errtst_parent(partid, shdata, data) VALUES ('10', '5', '5'); 2217ERROR: new row for relation "errtst_child_plaindef" violates check constraint "shdata_small" 2218DETAIL: Failing row contains (10, 5, 5). 2219INSERT INTO errtst_parent(partid, shdata, data) VALUES ('20', '5', '5'); 2220ERROR: new row for relation "errtst_child_reorder" violates check constraint "shdata_small" 2221DETAIL: Failing row contains (20, 5, 5). 2222-- within partition update without child check constraint violation 2223BEGIN; 2224UPDATE errtst_parent SET data = data + 1 WHERE partid = 0; 2225UPDATE errtst_parent SET data = data + 1 WHERE partid = 10; 2226UPDATE errtst_parent SET data = data + 1 WHERE partid = 20; 2227ROLLBACK; 2228-- within partition update with child check constraint violation 2229UPDATE errtst_parent SET data = data + 10 WHERE partid = 0; 2230ERROR: new row for relation "errtst_child_fastdef" violates check constraint "errtest_child_fastdef_data_check" 2231DETAIL: Failing row contains (0, 1, 15). 2232UPDATE errtst_parent SET data = data + 10 WHERE partid = 10; 2233ERROR: new row for relation "errtst_child_plaindef" violates check constraint "errtst_child_plaindef_data_check" 2234DETAIL: Failing row contains (10, 1, 15). 2235UPDATE errtst_parent SET data = data + 10 WHERE partid = 20; 2236ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check" 2237DETAIL: Failing row contains (15, 1, 20). 2238-- direct leaf partition update, without partition id violation 2239BEGIN; 2240UPDATE errtst_child_fastdef SET partid = 1 WHERE partid = 0; 2241UPDATE errtst_child_plaindef SET partid = 11 WHERE partid = 10; 2242UPDATE errtst_child_reorder SET partid = 21 WHERE partid = 20; 2243ROLLBACK; 2244-- direct leaf partition update, with partition id violation 2245UPDATE errtst_child_fastdef SET partid = partid + 10 WHERE partid = 0; 2246ERROR: new row for relation "errtst_child_fastdef" violates partition constraint 2247DETAIL: Failing row contains (10, 1, 5). 2248UPDATE errtst_child_plaindef SET partid = partid + 10 WHERE partid = 10; 2249ERROR: new row for relation "errtst_child_plaindef" violates partition constraint 2250DETAIL: Failing row contains (20, 1, 5). 2251UPDATE errtst_child_reorder SET partid = partid + 10 WHERE partid = 20; 2252ERROR: new row for relation "errtst_child_reorder" violates partition constraint 2253DETAIL: Failing row contains (5, 1, 30). 2254-- partition move, without child check constraint violation 2255UPDATE errtst_parent SET partid = 10, data = data + 1 WHERE partid = 0; 2256ERROR: new row for relation "errtst_child_fastdef" violates partition constraint 2257DETAIL: Failing row contains (10, 1, 6). 2258UPDATE errtst_parent SET partid = 20, data = data + 1 WHERE partid = 10; 2259ERROR: new row for relation "errtst_child_plaindef" violates partition constraint 2260DETAIL: Failing row contains (20, 1, 6). 2261UPDATE errtst_parent SET partid = 0, data = data + 1 WHERE partid = 20; 2262ERROR: new row for relation "errtst_child_reorder" violates partition constraint 2263DETAIL: Failing row contains (6, 1, 0). 2264-- partition move, with child check constraint violation 2265UPDATE errtst_parent SET partid = 10, data = data + 10 WHERE partid = 0; 2266ERROR: new row for relation "errtst_child_fastdef" violates check constraint "errtest_child_fastdef_data_check" 2267DETAIL: Failing row contains (10, 1, 15). 2268UPDATE errtst_parent SET partid = 20, data = data + 10 WHERE partid = 10; 2269ERROR: new row for relation "errtst_child_plaindef" violates check constraint "errtst_child_plaindef_data_check" 2270DETAIL: Failing row contains (20, 1, 15). 2271UPDATE errtst_parent SET partid = 0, data = data + 10 WHERE partid = 20; 2272ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check" 2273DETAIL: Failing row contains (15, 1, 0). 2274-- partition move, without target partition 2275UPDATE errtst_parent SET partid = 30, data = data + 10 WHERE partid = 20; 2276ERROR: new row for relation "errtst_child_reorder" violates check constraint "errtst_child_reorder_data_check" 2277DETAIL: Failing row contains (15, 1, 30). 2278DROP TABLE errtst_parent; 2279