2 -- Test inheritance features
4 CREATE TABLE a (aa TEXT);
5 CREATE TABLE b (bb TEXT) INHERITS (a);
6 CREATE TABLE c (cc TEXT) INHERITS (a);
7 CREATE TABLE d (dd TEXT) INHERITS (b,c,a);
8 NOTICE: merging multiple inherited definitions of column "aa"
9 NOTICE: merging multiple inherited definitions of column "aa"
10 INSERT INTO a(aa) VALUES('aaa');
11 INSERT INTO a(aa) VALUES('aaaa');
12 INSERT INTO a(aa) VALUES('aaaaa');
13 INSERT INTO a(aa) VALUES('aaaaaa');
14 INSERT INTO a(aa) VALUES('aaaaaaa');
15 INSERT INTO a(aa) VALUES('aaaaaaaa');
16 INSERT INTO b(aa) VALUES('bbb');
17 INSERT INTO b(aa) VALUES('bbbb');
18 INSERT INTO b(aa) VALUES('bbbbb');
19 INSERT INTO b(aa) VALUES('bbbbbb');
20 INSERT INTO b(aa) VALUES('bbbbbbb');
21 INSERT INTO b(aa) VALUES('bbbbbbbb');
22 INSERT INTO c(aa) VALUES('ccc');
23 INSERT INTO c(aa) VALUES('cccc');
24 INSERT INTO c(aa) VALUES('ccccc');
25 INSERT INTO c(aa) VALUES('cccccc');
26 INSERT INTO c(aa) VALUES('ccccccc');
27 INSERT INTO c(aa) VALUES('cccccccc');
28 INSERT INTO d(aa) VALUES('ddd');
29 INSERT INTO d(aa) VALUES('dddd');
30 INSERT INTO d(aa) VALUES('ddddd');
31 INSERT INTO d(aa) VALUES('dddddd');
32 INSERT INTO d(aa) VALUES('ddddddd');
33 INSERT INTO d(aa) VALUES('dddddddd');
34 SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
63 SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
65 ---------+----------+----
80 SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
82 ---------+----------+----
97 SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
98 relname | aa | bb | cc | dd
99 ---------+----------+----+----+----
108 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
119 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
121 ---------+----------+----
130 SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
132 ---------+----------+----
141 SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
142 relname | aa | bb | cc | dd
143 ---------+----------+----+----+----
152 UPDATE a SET aa='zzzz' WHERE aa='aaaa';
153 UPDATE ONLY a SET aa='zzzzz' WHERE aa='aaaaa';
154 UPDATE b SET aa='zzz' WHERE aa='aaa';
155 UPDATE ONLY b SET aa='zzz' WHERE aa='aaa';
156 UPDATE a SET aa='zzzzzz' WHERE aa LIKE 'aaa%';
157 SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
186 SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
188 ---------+----------+----
203 SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
205 ---------+----------+----
220 SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
221 relname | aa | bb | cc | dd
222 ---------+----------+----+----+----
231 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
242 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
244 ---------+----------+----
253 SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
255 ---------+----------+----
264 SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
265 relname | aa | bb | cc | dd
266 ---------+----------+----+----+----
275 UPDATE b SET aa='new';
276 SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
305 SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
322 SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
324 ---------+----------+----
339 SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
340 relname | aa | bb | cc | dd
341 ---------+-----+----+----+----
350 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
361 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
372 SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
374 ---------+----------+----
383 SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
384 relname | aa | bb | cc | dd
385 ---------+-----+----+----+----
394 UPDATE a SET aa='new';
395 DELETE FROM ONLY c WHERE aa='new';
396 SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
419 SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
436 SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
447 SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
448 relname | aa | bb | cc | dd
449 ---------+-----+----+----+----
458 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
469 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
480 SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
485 SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
486 relname | aa | bb | cc | dd
487 ---------+-----+----+----+----
497 SELECT relname, a.* FROM a, pg_class where a.tableoid = pg_class.oid;
502 SELECT relname, b.* FROM b, pg_class where b.tableoid = pg_class.oid;
507 SELECT relname, c.* FROM c, pg_class where c.tableoid = pg_class.oid;
512 SELECT relname, d.* FROM d, pg_class where d.tableoid = pg_class.oid;
513 relname | aa | bb | cc | dd
514 ---------+----+----+----+----
517 SELECT relname, a.* FROM ONLY a, pg_class where a.tableoid = pg_class.oid;
522 SELECT relname, b.* FROM ONLY b, pg_class where b.tableoid = pg_class.oid;
527 SELECT relname, c.* FROM ONLY c, pg_class where c.tableoid = pg_class.oid;
532 SELECT relname, d.* FROM ONLY d, pg_class where d.tableoid = pg_class.oid;
533 relname | aa | bb | cc | dd
534 ---------+----+----+----+----
537 -- Confirm PRIMARY KEY adds NOT NULL constraint to child table
538 CREATE TEMP TABLE z (b TEXT, PRIMARY KEY(aa, b)) inherits (a);
539 INSERT INTO z VALUES (NULL, 'text'); -- should fail
540 ERROR: null value in column "aa" violates not-null constraint
541 DETAIL: Failing row contains (null, text).
542 -- Check UPDATE with inherited target and an inherited source table
543 create temp table foo(f1 int, f2 int);
544 create temp table foo2(f3 int) inherits (foo);
545 create temp table bar(f1 int, f2 int);
546 create temp table bar2(f3 int) inherits (bar);
547 insert into foo values(1,1);
548 insert into foo values(3,3);
549 insert into foo2 values(2,2,2);
550 insert into foo2 values(3,3,3);
551 insert into bar values(1,1);
552 insert into bar values(2,2);
553 insert into bar values(3,3);
554 insert into bar values(4,4);
555 insert into bar2 values(1,1,1);
556 insert into bar2 values(2,2,2);
557 insert into bar2 values(3,3,3);
558 insert into bar2 values(4,4,4);
559 update bar set f2 = f2 + 100 where f1 in (select f1 from foo);
560 select tableoid::regclass::text as relname, bar.* from bar order by 1,2;
573 -- Check UPDATE with inherited target and an appendrel subquery
574 update bar set f2 = f2 + 100
576 ( select f1 from foo union all select f1+3 from foo ) ss
577 where bar.f1 = ss.f1;
578 select tableoid::regclass::text as relname, bar.* from bar order by 1,2;
591 /* Test multiple inheritance of column defaults */
592 CREATE TABLE firstparent (tomorrow date default now()::date + 1);
593 CREATE TABLE secondparent (tomorrow date default now() :: date + 1);
594 CREATE TABLE jointchild () INHERITS (firstparent, secondparent); -- ok
595 NOTICE: merging multiple inherited definitions of column "tomorrow"
596 CREATE TABLE thirdparent (tomorrow date default now()::date - 1);
597 CREATE TABLE otherchild () INHERITS (firstparent, thirdparent); -- not ok
598 NOTICE: merging multiple inherited definitions of column "tomorrow"
599 ERROR: column "tomorrow" inherits conflicting default values
600 HINT: To resolve the conflict, specify a default explicitly.
601 CREATE TABLE otherchild (tomorrow date default now())
602 INHERITS (firstparent, thirdparent); -- ok, child resolves ambiguous default
603 NOTICE: merging multiple inherited definitions of column "tomorrow"
604 NOTICE: merging column "tomorrow" with inherited definition
605 DROP TABLE firstparent, secondparent, jointchild, thirdparent, otherchild;
606 -- Test changing the type of inherited columns
607 insert into d values('test','one','two','three');
608 alter table a alter column aa type integer using bit_length(aa);
611 ----+-----+-----+-------
612 32 | one | two | three
615 -- check that oid column is handled properly during alter table inherit
616 create table oid_parent (a int) with oids;
617 create table oid_child () inherits (oid_parent);
618 select attinhcount, attislocal from pg_attribute
619 where attrelid = 'oid_child'::regclass and attname = 'oid';
620 attinhcount | attislocal
621 -------------+------------
625 drop table oid_child;
626 create table oid_child (a int) without oids;
627 alter table oid_child inherit oid_parent; -- fail
628 ERROR: table "oid_child" without OIDs cannot inherit from table "oid_parent" with OIDs
629 alter table oid_child set with oids;
630 select attinhcount, attislocal from pg_attribute
631 where attrelid = 'oid_child'::regclass and attname = 'oid';
632 attinhcount | attislocal
633 -------------+------------
637 alter table oid_child inherit oid_parent;
638 select attinhcount, attislocal from pg_attribute
639 where attrelid = 'oid_child'::regclass and attname = 'oid';
640 attinhcount | attislocal
641 -------------+------------
645 alter table oid_child set without oids; -- fail
646 ERROR: cannot drop inherited column "oid"
647 alter table oid_parent set without oids;
648 select attinhcount, attislocal from pg_attribute
649 where attrelid = 'oid_child'::regclass and attname = 'oid';
650 attinhcount | attislocal
651 -------------+------------
655 alter table oid_child set without oids;
656 select attinhcount, attislocal from pg_attribute
657 where attrelid = 'oid_child'::regclass and attname = 'oid';
658 attinhcount | attislocal
659 -------------+------------
662 drop table oid_parent cascade;
663 NOTICE: drop cascades to table oid_child
664 -- Test non-inheritable parent constraints
665 create table p1(ff1 int);
666 alter table p1 add constraint p1chk check (ff1 > 0) no inherit;
667 alter table p1 add constraint p2chk check (ff1 > 10);
668 -- connoinherit should be true for NO INHERIT constraint
669 select 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;
670 relname | conname | contype | conislocal | coninhcount | connoinherit
671 ---------+---------+---------+------------+-------------+--------------
672 p1 | p1chk | c | t | 0 | t
673 p1 | p2chk | c | t | 0 | f
676 -- Test that child does not inherit NO INHERIT constraints
677 create table c1 () inherits (p1);
680 Column | Type | Collation | Nullable | Default
681 --------+---------+-----------+----------+---------
684 "p1chk" CHECK (ff1 > 0) NO INHERIT
685 "p2chk" CHECK (ff1 > 10)
686 Number of child tables: 1 (Use \d+ to list them.)
690 Column | Type | Collation | Nullable | Default
691 --------+---------+-----------+----------+---------
694 "p2chk" CHECK (ff1 > 10)
697 -- Test that child does not override inheritable constraints of the parent
698 create table c2 (constraint p2chk check (ff1 > 10) no inherit) inherits (p1); --fails
699 ERROR: constraint "p2chk" conflicts with inherited constraint on relation "c2"
700 drop table p1 cascade;
701 NOTICE: drop cascades to table c1
702 -- Tests for casting between the rowtypes of parent and child
703 -- tables. See the pgsql-hackers thread beginning Dec. 4/04
704 create table base (i integer);
705 create table derived () inherits (base);
706 insert into derived (i) values (0);
707 select derived::base from derived;
713 select NULL::derived::base;
721 create table p1(ff1 int);
722 create table p2(f1 text);
723 create function p2text(p2) returns text as 'select $1.f1' language sql;
724 create table c1(f3 int) inherits(p1,p2);
725 insert into c1 values(123456789, 'hi', 42);
726 select p2text(c1.*) from c1;
732 drop function p2text(p2);
736 CREATE TABLE ac (aa TEXT);
737 alter table ac add constraint ac_check check (aa is not null);
738 CREATE TABLE bc (bb TEXT) INHERITS (ac);
739 select 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;
740 relname | conname | contype | conislocal | coninhcount | consrc
741 ---------+----------+---------+------------+-------------+------------------
742 ac | ac_check | c | t | 0 | (aa IS NOT NULL)
743 bc | ac_check | c | f | 1 | (aa IS NOT NULL)
746 insert into ac (aa) values (NULL);
747 ERROR: new row for relation "ac" violates check constraint "ac_check"
748 DETAIL: Failing row contains (null).
749 insert into bc (aa) values (NULL);
750 ERROR: new row for relation "bc" violates check constraint "ac_check"
751 DETAIL: Failing row contains (null, null).
752 alter table bc drop constraint ac_check; -- fail, disallowed
753 ERROR: cannot drop inherited constraint "ac_check" of relation "bc"
754 alter table ac drop constraint ac_check;
755 select 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;
756 relname | conname | contype | conislocal | coninhcount | consrc
757 ---------+---------+---------+------------+-------------+--------
760 -- try the unnamed-constraint case
761 alter table ac add check (aa is not null);
762 select 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;
763 relname | conname | contype | conislocal | coninhcount | consrc
764 ---------+-------------+---------+------------+-------------+------------------
765 ac | ac_aa_check | c | t | 0 | (aa IS NOT NULL)
766 bc | ac_aa_check | c | f | 1 | (aa IS NOT NULL)
769 insert into ac (aa) values (NULL);
770 ERROR: new row for relation "ac" violates check constraint "ac_aa_check"
771 DETAIL: Failing row contains (null).
772 insert into bc (aa) values (NULL);
773 ERROR: new row for relation "bc" violates check constraint "ac_aa_check"
774 DETAIL: Failing row contains (null, null).
775 alter table bc drop constraint ac_aa_check; -- fail, disallowed
776 ERROR: cannot drop inherited constraint "ac_aa_check" of relation "bc"
777 alter table ac drop constraint ac_aa_check;
778 select 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;
779 relname | conname | contype | conislocal | coninhcount | consrc
780 ---------+---------+---------+------------+-------------+--------
783 alter table ac add constraint ac_check check (aa is not null);
784 alter table bc no inherit ac;
785 select 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;
786 relname | conname | contype | conislocal | coninhcount | consrc
787 ---------+----------+---------+------------+-------------+------------------
788 ac | ac_check | c | t | 0 | (aa IS NOT NULL)
789 bc | ac_check | c | t | 0 | (aa IS NOT NULL)
792 alter table bc drop constraint ac_check;
793 select 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;
794 relname | conname | contype | conislocal | coninhcount | consrc
795 ---------+----------+---------+------------+-------------+------------------
796 ac | ac_check | c | t | 0 | (aa IS NOT NULL)
799 alter table ac drop constraint ac_check;
800 select 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;
801 relname | conname | contype | conislocal | coninhcount | consrc
802 ---------+---------+---------+------------+-------------+--------
807 create table ac (a int constraint check_a check (a <> 0));
808 create table bc (a int constraint check_a check (a <> 0), b int constraint check_b check (b <> 0)) inherits (ac);
809 NOTICE: merging column "a" with inherited definition
810 NOTICE: merging constraint "check_a" with inherited definition
811 select 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;
812 relname | conname | contype | conislocal | coninhcount | consrc
813 ---------+---------+---------+------------+-------------+----------
814 ac | check_a | c | t | 0 | (a <> 0)
815 bc | check_a | c | t | 1 | (a <> 0)
816 bc | check_b | c | t | 0 | (b <> 0)
821 create table ac (a int constraint check_a check (a <> 0));
822 create table bc (b int constraint check_b check (b <> 0));
823 create table cc (c int constraint check_c check (c <> 0)) inherits (ac, bc);
824 select 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;
825 relname | conname | contype | conislocal | coninhcount | consrc
826 ---------+---------+---------+------------+-------------+----------
827 ac | check_a | c | t | 0 | (a <> 0)
828 bc | check_b | c | t | 0 | (b <> 0)
829 cc | check_a | c | f | 1 | (a <> 0)
830 cc | check_b | c | f | 1 | (b <> 0)
831 cc | check_c | c | t | 0 | (c <> 0)
834 alter table cc no inherit bc;
835 select 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;
836 relname | conname | contype | conislocal | coninhcount | consrc
837 ---------+---------+---------+------------+-------------+----------
838 ac | check_a | c | t | 0 | (a <> 0)
839 bc | check_b | c | t | 0 | (b <> 0)
840 cc | check_a | c | f | 1 | (a <> 0)
841 cc | check_b | c | t | 0 | (b <> 0)
842 cc | check_c | c | t | 0 | (c <> 0)
848 create table p1(f1 int);
849 create table p2(f2 int);
850 create table c1(f3 int) inherits(p1,p2);
851 insert into c1 values(1,-1,2);
852 alter table p2 add constraint cc check (f2>0); -- fail
853 ERROR: check constraint "cc" is violated by some row
854 alter table p2 add check (f2>0); -- check it without a name, too
855 ERROR: check constraint "p2_f2_check" is violated by some row
857 insert into c1 values(1,1,2);
858 alter table p2 add check (f2>0);
859 insert into c1 values(1,-1,2); -- fail
860 ERROR: new row for relation "c1" violates check constraint "p2_f2_check"
861 DETAIL: Failing row contains (1, -1, 2).
862 create table c2(f3 int) inherits(p1,p2);
865 Column | Type | Collation | Nullable | Default
866 --------+---------+-----------+----------+---------
871 "p2_f2_check" CHECK (f2 > 0)
875 create table c3 (f4 int) inherits(c1,c2);
876 NOTICE: merging multiple inherited definitions of column "f1"
877 NOTICE: merging multiple inherited definitions of column "f2"
878 NOTICE: merging multiple inherited definitions of column "f3"
881 Column | Type | Collation | Nullable | Default
882 --------+---------+-----------+----------+---------
888 "p2_f2_check" CHECK (f2 > 0)
892 drop table p1 cascade;
893 NOTICE: drop cascades to 3 other objects
894 DETAIL: drop cascades to table c1
895 drop cascades to table c2
896 drop cascades to table c3
897 drop table p2 cascade;
898 create table pp1 (f1 int);
899 create table cc1 (f2 text, f3 int) inherits (pp1);
900 alter table pp1 add column a1 int check (a1 > 0);
903 Column | Type | Collation | Nullable | Default
904 --------+---------+-----------+----------+---------
910 "pp1_a1_check" CHECK (a1 > 0)
913 create table cc2(f4 float) inherits(pp1,cc1);
914 NOTICE: merging multiple inherited definitions of column "f1"
915 NOTICE: merging multiple inherited definitions of column "a1"
918 Column | Type | Collation | Nullable | Default
919 --------+------------------+-----------+----------+---------
924 f4 | double precision | | |
926 "pp1_a1_check" CHECK (a1 > 0)
930 alter table pp1 add column a2 int check (a2 > 0);
931 NOTICE: merging definition of column "a2" for child "cc2"
932 NOTICE: merging constraint "pp1_a2_check" with inherited definition
935 Column | Type | Collation | Nullable | Default
936 --------+------------------+-----------+----------+---------
941 f4 | double precision | | |
944 "pp1_a1_check" CHECK (a1 > 0)
945 "pp1_a2_check" CHECK (a2 > 0)
949 drop table pp1 cascade;
950 NOTICE: drop cascades to 2 other objects
951 DETAIL: drop cascades to table cc1
952 drop cascades to table cc2
953 -- Test for renaming in simple multiple inheritance
954 CREATE TABLE inht1 (a int, b int);
955 CREATE TABLE inhs1 (b int, c int);
956 CREATE TABLE inhts (d int) INHERITS (inht1, inhs1);
957 NOTICE: merging multiple inherited definitions of column "b"
958 ALTER TABLE inht1 RENAME a TO aa;
959 ALTER TABLE inht1 RENAME b TO bb; -- to be failed
960 ERROR: cannot rename inherited column "b"
961 ALTER TABLE inhts RENAME aa TO aaa; -- to be failed
962 ERROR: cannot rename inherited column "aa"
963 ALTER TABLE inhts RENAME d TO dd;
966 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
967 --------+---------+-----------+----------+---------+---------+--------------+-------------
968 aa | integer | | | | plain | |
969 b | integer | | | | plain | |
970 c | integer | | | | plain | |
971 dd | integer | | | | plain | |
976 -- Test for renaming in diamond inheritance
977 CREATE TABLE inht2 (x int) INHERITS (inht1);
978 CREATE TABLE inht3 (y int) INHERITS (inht1);
979 CREATE TABLE inht4 (z int) INHERITS (inht2, inht3);
980 NOTICE: merging multiple inherited definitions of column "aa"
981 NOTICE: merging multiple inherited definitions of column "b"
982 ALTER TABLE inht1 RENAME aa TO aaa;
985 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
986 --------+---------+-----------+----------+---------+---------+--------------+-------------
987 aaa | integer | | | | plain | |
988 b | integer | | | | plain | |
989 x | integer | | | | plain | |
990 y | integer | | | | plain | |
991 z | integer | | | | plain | |
995 CREATE TABLE inhts (d int) INHERITS (inht2, inhs1);
996 NOTICE: merging multiple inherited definitions of column "b"
997 ALTER TABLE inht1 RENAME aaa TO aaaa;
998 ALTER TABLE inht1 RENAME b TO bb; -- to be failed
999 ERROR: cannot rename inherited column "b"
1001 Table "public.inhts"
1002 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1003 --------+---------+-----------+----------+---------+---------+--------------+-------------
1004 aaaa | integer | | | | plain | |
1005 b | integer | | | | plain | |
1006 x | integer | | | | plain | |
1007 c | integer | | | | plain | |
1008 d | integer | | | | plain | |
1012 WITH RECURSIVE r AS (
1013 SELECT 'inht1'::regclass AS inhrelid
1015 SELECT c.inhrelid FROM pg_inherits c, r WHERE r.inhrelid = c.inhparent
1017 SELECT a.attrelid::regclass, a.attname, a.attinhcount, e.expected
1018 FROM (SELECT inhrelid, count(*) AS expected FROM pg_inherits
1019 WHERE inhparent IN (SELECT inhrelid FROM r) GROUP BY inhrelid) e
1020 JOIN pg_attribute a ON e.inhrelid = a.attrelid WHERE NOT attislocal
1021 ORDER BY a.attrelid::regclass::name, a.attnum;
1022 attrelid | attname | attinhcount | expected
1023 ----------+---------+-------------+----------
1024 inht2 | aaaa | 1 | 1
1026 inht3 | aaaa | 1 | 1
1028 inht4 | aaaa | 2 | 2
1032 inhts | aaaa | 1 | 1
1038 DROP TABLE inht1, inhs1 CASCADE;
1039 NOTICE: drop cascades to 4 other objects
1040 DETAIL: drop cascades to table inht2
1041 drop cascades to table inhts
1042 drop cascades to table inht3
1043 drop cascades to table inht4
1044 -- Test non-inheritable indices [UNIQUE, EXCLUDE] constraints
1045 CREATE TABLE test_constraints (id int, val1 varchar, val2 int, UNIQUE(val1, val2));
1046 CREATE TABLE test_constraints_inh () INHERITS (test_constraints);
1047 \d+ test_constraints
1048 Table "public.test_constraints"
1049 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1050 --------+-------------------+-----------+----------+---------+----------+--------------+-------------
1051 id | integer | | | | plain | |
1052 val1 | character varying | | | | extended | |
1053 val2 | integer | | | | plain | |
1055 "test_constraints_val1_val2_key" UNIQUE CONSTRAINT, btree (val1, val2)
1056 Child tables: test_constraints_inh
1058 ALTER TABLE ONLY test_constraints DROP CONSTRAINT test_constraints_val1_val2_key;
1059 \d+ test_constraints
1060 Table "public.test_constraints"
1061 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1062 --------+-------------------+-----------+----------+---------+----------+--------------+-------------
1063 id | integer | | | | plain | |
1064 val1 | character varying | | | | extended | |
1065 val2 | integer | | | | plain | |
1066 Child tables: test_constraints_inh
1068 \d+ test_constraints_inh
1069 Table "public.test_constraints_inh"
1070 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1071 --------+-------------------+-----------+----------+---------+----------+--------------+-------------
1072 id | integer | | | | plain | |
1073 val1 | character varying | | | | extended | |
1074 val2 | integer | | | | plain | |
1075 Inherits: test_constraints
1077 DROP TABLE test_constraints_inh;
1078 DROP TABLE test_constraints;
1079 CREATE TABLE test_ex_constraints (
1081 EXCLUDE USING gist (c WITH &&)
1083 CREATE TABLE test_ex_constraints_inh () INHERITS (test_ex_constraints);
1084 \d+ test_ex_constraints
1085 Table "public.test_ex_constraints"
1086 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1087 --------+--------+-----------+----------+---------+---------+--------------+-------------
1088 c | circle | | | | plain | |
1090 "test_ex_constraints_c_excl" EXCLUDE USING gist (c WITH &&)
1091 Child tables: test_ex_constraints_inh
1093 ALTER TABLE test_ex_constraints DROP CONSTRAINT test_ex_constraints_c_excl;
1094 \d+ test_ex_constraints
1095 Table "public.test_ex_constraints"
1096 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1097 --------+--------+-----------+----------+---------+---------+--------------+-------------
1098 c | circle | | | | plain | |
1099 Child tables: test_ex_constraints_inh
1101 \d+ test_ex_constraints_inh
1102 Table "public.test_ex_constraints_inh"
1103 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1104 --------+--------+-----------+----------+---------+---------+--------------+-------------
1105 c | circle | | | | plain | |
1106 Inherits: test_ex_constraints
1108 DROP TABLE test_ex_constraints_inh;
1109 DROP TABLE test_ex_constraints;
1110 -- Test non-inheritable foreign key constraints
1111 CREATE TABLE test_primary_constraints(id int PRIMARY KEY);
1112 CREATE TABLE test_foreign_constraints(id1 int REFERENCES test_primary_constraints(id));
1113 CREATE TABLE test_foreign_constraints_inh () INHERITS (test_foreign_constraints);
1114 \d+ test_primary_constraints
1115 Table "public.test_primary_constraints"
1116 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1117 --------+---------+-----------+----------+---------+---------+--------------+-------------
1118 id | integer | | not null | | plain | |
1120 "test_primary_constraints_pkey" PRIMARY KEY, btree (id)
1122 TABLE "test_foreign_constraints" CONSTRAINT "test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id)
1124 \d+ test_foreign_constraints
1125 Table "public.test_foreign_constraints"
1126 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1127 --------+---------+-----------+----------+---------+---------+--------------+-------------
1128 id1 | integer | | | | plain | |
1129 Foreign-key constraints:
1130 "test_foreign_constraints_id1_fkey" FOREIGN KEY (id1) REFERENCES test_primary_constraints(id)
1131 Child tables: test_foreign_constraints_inh
1133 ALTER TABLE test_foreign_constraints DROP CONSTRAINT test_foreign_constraints_id1_fkey;
1134 \d+ test_foreign_constraints
1135 Table "public.test_foreign_constraints"
1136 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1137 --------+---------+-----------+----------+---------+---------+--------------+-------------
1138 id1 | integer | | | | plain | |
1139 Child tables: test_foreign_constraints_inh
1141 \d+ test_foreign_constraints_inh
1142 Table "public.test_foreign_constraints_inh"
1143 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
1144 --------+---------+-----------+----------+---------+---------+--------------+-------------
1145 id1 | integer | | | | plain | |
1146 Inherits: test_foreign_constraints
1148 DROP TABLE test_foreign_constraints_inh;
1149 DROP TABLE test_foreign_constraints;
1150 DROP TABLE test_primary_constraints;
1151 -- Test that parent and child CHECK constraints can be created in either order
1152 create table p1(f1 int);
1153 create table p1_c1() inherits(p1);
1154 alter table p1 add constraint inh_check_constraint1 check (f1 > 0);
1155 alter table p1_c1 add constraint inh_check_constraint1 check (f1 > 0);
1156 NOTICE: merging constraint "inh_check_constraint1" with inherited definition
1157 alter table p1_c1 add constraint inh_check_constraint2 check (f1 < 10);
1158 alter table p1 add constraint inh_check_constraint2 check (f1 < 10);
1159 NOTICE: merging constraint "inh_check_constraint2" with inherited definition
1160 select conrelid::regclass::text as relname, conname, conislocal, coninhcount
1161 from pg_constraint where conname like 'inh\_check\_constraint%'
1163 relname | conname | conislocal | coninhcount
1164 ---------+-----------------------+------------+-------------
1165 p1 | inh_check_constraint1 | t | 0
1166 p1 | inh_check_constraint2 | t | 0
1167 p1_c1 | inh_check_constraint1 | t | 1
1168 p1_c1 | inh_check_constraint2 | t | 1
1171 drop table p1 cascade;
1172 NOTICE: drop cascades to table p1_c1
1173 -- Test that a valid child can have not-valid parent, but not vice versa
1174 create table invalid_check_con(f1 int);
1175 create table invalid_check_con_child() inherits(invalid_check_con);
1176 alter table invalid_check_con_child add constraint inh_check_constraint check(f1 > 0) not valid;
1177 alter table invalid_check_con add constraint inh_check_constraint check(f1 > 0); -- fail
1178 ERROR: constraint "inh_check_constraint" conflicts with NOT VALID constraint on relation "invalid_check_con_child"
1179 alter table invalid_check_con_child drop constraint inh_check_constraint;
1180 insert into invalid_check_con values(0);
1181 alter table invalid_check_con_child add constraint inh_check_constraint check(f1 > 0);
1182 alter table invalid_check_con add constraint inh_check_constraint check(f1 > 0) not valid;
1183 NOTICE: merging constraint "inh_check_constraint" with inherited definition
1184 insert into invalid_check_con values(0); -- fail
1185 ERROR: new row for relation "invalid_check_con" violates check constraint "inh_check_constraint"
1186 DETAIL: Failing row contains (0).
1187 insert into invalid_check_con_child values(0); -- fail
1188 ERROR: new row for relation "invalid_check_con_child" violates check constraint "inh_check_constraint"
1189 DETAIL: Failing row contains (0).
1190 select conrelid::regclass::text as relname, conname,
1191 convalidated, conislocal, coninhcount, connoinherit
1192 from pg_constraint where conname like 'inh\_check\_constraint%'
1194 relname | conname | convalidated | conislocal | coninhcount | connoinherit
1195 -------------------------+----------------------+--------------+------------+-------------+--------------
1196 invalid_check_con | inh_check_constraint | f | t | 0 | f
1197 invalid_check_con_child | inh_check_constraint | t | t | 1 | f
1200 -- We don't drop the invalid_check_con* tables, to test dump/reload with
1202 -- Test parameterized append plans for inheritance trees
1204 create temp table patest0 (id, x) as
1205 select x, x from generate_series(0,1000) x;
1206 create temp table patest1() inherits (patest0);
1208 select x, x from generate_series(0,1000) x;
1209 create temp table patest2() inherits (patest0);
1211 select x, x from generate_series(0,1000) x;
1212 create index patest0i on patest0(id);
1213 create index patest1i on patest1(id);
1214 create index patest2i on patest2(id);
1219 select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1;
1221 --------------------------------------------------
1224 -> Seq Scan on int4_tbl
1226 -> Index Scan using patest0i on patest0
1227 Index Cond: (id = int4_tbl.f1)
1228 -> Index Scan using patest1i on patest1
1229 Index Cond: (id = int4_tbl.f1)
1230 -> Index Scan using patest2i on patest2
1231 Index Cond: (id = int4_tbl.f1)
1234 select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1;
1242 drop index patest2i;
1244 select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1;
1246 --------------------------------------------------
1249 -> Seq Scan on int4_tbl
1251 -> Index Scan using patest0i on patest0
1252 Index Cond: (id = int4_tbl.f1)
1253 -> Index Scan using patest1i on patest1
1254 Index Cond: (id = int4_tbl.f1)
1255 -> Seq Scan on patest2
1256 Filter: (int4_tbl.f1 = id)
1259 select * from patest0 join (select f1 from int4_tbl limit 1) ss on id = f1;
1267 drop table patest0 cascade;
1268 NOTICE: drop cascades to 2 other objects
1269 DETAIL: drop cascades to table patest1
1270 drop cascades to table patest2
1272 -- Test merge-append plans for inheritance trees
1274 create table matest0 (id serial primary key, name text);
1275 create table matest1 (id integer primary key) inherits (matest0);
1276 NOTICE: merging column "id" with inherited definition
1277 create table matest2 (id integer primary key) inherits (matest0);
1278 NOTICE: merging column "id" with inherited definition
1279 create table matest3 (id integer primary key) inherits (matest0);
1280 NOTICE: merging column "id" with inherited definition
1281 create index matest0i on matest0 ((1-id));
1282 create index matest1i on matest1 ((1-id));
1283 -- create index matest2i on matest2 ((1-id)); -- intentionally missing
1284 create index matest3i on matest3 ((1-id));
1285 insert into matest1 (name) values ('Test 1');
1286 insert into matest1 (name) values ('Test 2');
1287 insert into matest2 (name) values ('Test 3');
1288 insert into matest2 (name) values ('Test 4');
1289 insert into matest3 (name) values ('Test 5');
1290 insert into matest3 (name) values ('Test 6');
1291 set enable_indexscan = off; -- force use of seqscan/sort, so no merge
1292 explain (verbose, costs off) select * from matest0 order by 1-id;
1294 ------------------------------------------------------------
1296 Output: matest0.id, matest0.name, ((1 - matest0.id))
1297 Sort Key: ((1 - matest0.id))
1299 Output: matest0.id, matest0.name, (1 - matest0.id)
1301 -> Seq Scan on public.matest0
1302 Output: matest0.id, matest0.name
1303 -> Seq Scan on public.matest1
1304 Output: matest1.id, matest1.name
1305 -> Seq Scan on public.matest2
1306 Output: matest2.id, matest2.name
1307 -> Seq Scan on public.matest3
1308 Output: matest3.id, matest3.name
1311 select * from matest0 order by 1-id;
1322 explain (verbose, costs off) select min(1-id) from matest0;
1324 ----------------------------------------
1326 Output: min((1 - matest0.id))
1328 -> Seq Scan on public.matest0
1330 -> Seq Scan on public.matest1
1332 -> Seq Scan on public.matest2
1334 -> Seq Scan on public.matest3
1338 select min(1-id) from matest0;
1344 reset enable_indexscan;
1345 set enable_seqscan = off; -- plan with fewest seqscans should be merge
1346 explain (verbose, costs off) select * from matest0 order by 1-id;
1348 ------------------------------------------------------------------
1350 Sort Key: ((1 - matest0.id))
1351 -> Index Scan using matest0i on public.matest0
1352 Output: matest0.id, matest0.name, (1 - matest0.id)
1353 -> Index Scan using matest1i on public.matest1
1354 Output: matest1.id, matest1.name, (1 - matest1.id)
1356 Output: matest2.id, matest2.name, ((1 - matest2.id))
1357 Sort Key: ((1 - matest2.id))
1358 -> Seq Scan on public.matest2
1359 Output: matest2.id, matest2.name, (1 - matest2.id)
1360 -> Index Scan using matest3i on public.matest3
1361 Output: matest3.id, matest3.name, (1 - matest3.id)
1364 select * from matest0 order by 1-id;
1375 explain (verbose, costs off) select min(1-id) from matest0;
1377 --------------------------------------------------------------------------
1380 InitPlan 1 (returns $0)
1382 Output: ((1 - matest0.id))
1384 Output: ((1 - matest0.id))
1386 Sort Key: ((1 - matest0.id))
1387 -> Index Scan using matest0i on public.matest0
1388 Output: matest0.id, (1 - matest0.id)
1389 Index Cond: ((1 - matest0.id) IS NOT NULL)
1390 -> Index Scan using matest1i on public.matest1
1391 Output: matest1.id, (1 - matest1.id)
1392 Index Cond: ((1 - matest1.id) IS NOT NULL)
1394 Output: matest2.id, ((1 - matest2.id))
1395 Sort Key: ((1 - matest2.id))
1396 -> Bitmap Heap Scan on public.matest2
1397 Output: matest2.id, (1 - matest2.id)
1398 Filter: ((1 - matest2.id) IS NOT NULL)
1399 -> Bitmap Index Scan on matest2_pkey
1400 -> Index Scan using matest3i on public.matest3
1401 Output: matest3.id, (1 - matest3.id)
1402 Index Cond: ((1 - matest3.id) IS NOT NULL)
1405 select min(1-id) from matest0;
1411 reset enable_seqscan;
1412 drop table matest0 cascade;
1413 NOTICE: drop cascades to 3 other objects
1414 DETAIL: drop cascades to table matest1
1415 drop cascades to table matest2
1416 drop cascades to table matest3
1418 -- Check that use of an index with an extraneous column doesn't produce
1419 -- a plan with extraneous sorting
1421 create table matest0 (a int, b int, c int, d int);
1422 create table matest1 () inherits(matest0);
1423 create index matest0i on matest0 (b, c);
1424 create index matest1i on matest1 (b, c);
1425 set enable_nestloop = off; -- we want a plan with two MergeAppends
1427 select t1.* from matest0 t1, matest0 t2
1428 where t1.b = t2.b and t2.c = t2.d
1429 order by t1.b limit 10;
1431 -------------------------------------------------------------------
1434 Merge Cond: (t1.b = t2.b)
1437 -> Index Scan using matest0i on matest0 t1
1438 -> Index Scan using matest1i on matest1 t1_1
1442 -> Index Scan using matest0i on matest0 t2
1444 -> Index Scan using matest1i on matest1 t2_1
1448 reset enable_nestloop;
1449 drop table matest0 cascade;
1450 NOTICE: drop cascades to table matest1
1452 -- Test merge-append for UNION ALL append relations
1454 set enable_seqscan = off;
1455 set enable_indexscan = on;
1456 set enable_bitmapscan = off;
1457 -- Check handling of duplicated, constant, or volatile targetlist items
1459 SELECT thousand, tenthous FROM tenk1
1461 SELECT thousand, thousand FROM tenk1
1462 ORDER BY thousand, tenthous;
1464 -------------------------------------------------------------------------
1466 Sort Key: tenk1.thousand, tenk1.tenthous
1467 -> Index Only Scan using tenk1_thous_tenthous on tenk1
1469 Sort Key: tenk1_1.thousand, tenk1_1.thousand
1470 -> Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1
1474 SELECT thousand, tenthous, thousand+tenthous AS x FROM tenk1
1476 SELECT 42, 42, hundred FROM tenk1
1477 ORDER BY thousand, tenthous;
1479 ------------------------------------------------------------------
1481 Sort Key: tenk1.thousand, tenk1.tenthous
1482 -> Index Only Scan using tenk1_thous_tenthous on tenk1
1485 -> Index Only Scan using tenk1_hundred on tenk1 tenk1_1
1489 SELECT thousand, tenthous FROM tenk1
1491 SELECT thousand, random()::integer FROM tenk1
1492 ORDER BY thousand, tenthous;
1494 -------------------------------------------------------------------------
1496 Sort Key: tenk1.thousand, tenk1.tenthous
1497 -> Index Only Scan using tenk1_thous_tenthous on tenk1
1499 Sort Key: tenk1_1.thousand, ((random())::integer)
1500 -> Index Only Scan using tenk1_thous_tenthous on tenk1 tenk1_1
1503 -- Check min/max aggregate optimization
1506 (SELECT unique1 AS x FROM tenk1 a
1508 SELECT unique2 AS x FROM tenk1 b) s;
1510 --------------------------------------------------------------------
1512 InitPlan 1 (returns $0)
1516 -> Index Only Scan using tenk1_unique1 on tenk1 a
1517 Index Cond: (unique1 IS NOT NULL)
1518 -> Index Only Scan using tenk1_unique2 on tenk1 b
1519 Index Cond: (unique2 IS NOT NULL)
1524 (SELECT unique1 AS x, unique1 AS y FROM tenk1 a
1526 SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s;
1528 --------------------------------------------------------------------
1530 InitPlan 1 (returns $0)
1534 -> Index Only Scan using tenk1_unique1 on tenk1 a
1535 Index Cond: (unique1 IS NOT NULL)
1536 -> Index Only Scan using tenk1_unique2 on tenk1 b
1537 Index Cond: (unique2 IS NOT NULL)
1540 -- XXX planner doesn't recognize that index on unique2 is sufficiently sorted
1543 (SELECT thousand AS x, tenthous AS y FROM tenk1 a
1545 SELECT unique2 AS x, unique2 AS y FROM tenk1 b) s
1548 -------------------------------------------------------------
1550 Sort Key: a.thousand, a.tenthous
1551 -> Index Only Scan using tenk1_thous_tenthous on tenk1 a
1553 Sort Key: b.unique2, b.unique2
1554 -> Index Only Scan using tenk1_unique2 on tenk1 b
1557 -- exercise rescan code path via a repeatedly-evaluated subquery
1560 ARRAY(SELECT f.i FROM (
1561 (SELECT d + g.i FROM generate_series(4, 30, 3) d ORDER BY 1)
1563 (SELECT d + g.i FROM generate_series(0, 30, 5) d ORDER BY 1)
1565 ORDER BY f.i LIMIT 10)
1566 FROM generate_series(1, 3) g(i);
1568 ----------------------------------------------------------------
1569 Function Scan on generate_series g
1573 Sort Key: ((d.d + g.i))
1575 Sort Key: ((d.d + g.i))
1576 -> Function Scan on generate_series d
1578 Sort Key: ((d_1.d + g.i))
1579 -> Function Scan on generate_series d_1
1583 ARRAY(SELECT f.i FROM (
1584 (SELECT d + g.i FROM generate_series(4, 30, 3) d ORDER BY 1)
1586 (SELECT d + g.i FROM generate_series(0, 30, 5) d ORDER BY 1)
1588 ORDER BY f.i LIMIT 10)
1589 FROM generate_series(1, 3) g(i);
1591 ------------------------------
1592 {1,5,6,8,11,11,14,16,17,20}
1593 {2,6,7,9,12,12,15,17,18,21}
1594 {3,7,8,10,13,13,16,18,19,22}
1597 reset enable_seqscan;
1598 reset enable_indexscan;
1599 reset enable_bitmapscan;
1601 -- Check that constraint exclusion works correctly with partitions using
1602 -- implicit constraints generated from the partition bound information.
1604 create table list_parted (
1606 ) partition by list (a);
1607 create table part_ab_cd partition of list_parted for values in ('ab', 'cd');
1608 create table part_ef_gh partition of list_parted for values in ('ef', 'gh');
1609 create table part_null_xy partition of list_parted for values in (null, 'xy');
1610 explain (costs off) select * from list_parted;
1612 --------------------------------
1614 -> Seq Scan on list_parted
1615 -> Seq Scan on part_ab_cd
1616 -> Seq Scan on part_ef_gh
1617 -> Seq Scan on part_null_xy
1620 explain (costs off) select * from list_parted where a is null;
1622 --------------------------------
1624 -> Seq Scan on list_parted
1626 -> Seq Scan on part_null_xy
1630 explain (costs off) select * from list_parted where a is not null;
1632 ---------------------------------
1634 -> Seq Scan on list_parted
1635 Filter: (a IS NOT NULL)
1636 -> Seq Scan on part_ab_cd
1637 Filter: (a IS NOT NULL)
1638 -> Seq Scan on part_ef_gh
1639 Filter: (a IS NOT NULL)
1640 -> Seq Scan on part_null_xy
1641 Filter: (a IS NOT NULL)
1644 explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef');
1646 ----------------------------------------------------------
1648 -> Seq Scan on list_parted
1649 Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[]))
1650 -> Seq Scan on part_ab_cd
1651 Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[]))
1652 -> Seq Scan on part_ef_gh
1653 Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[]))
1656 explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd');
1658 ---------------------------------------------------------------------------------------
1660 -> Seq Scan on list_parted
1661 Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[])))
1662 -> Seq Scan on part_ab_cd
1663 Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[])))
1664 -> Seq Scan on part_ef_gh
1665 Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[])))
1666 -> Seq Scan on part_null_xy
1667 Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[])))
1670 explain (costs off) select * from list_parted where a = 'ab';
1672 ------------------------------------------
1674 -> Seq Scan on list_parted
1675 Filter: ((a)::text = 'ab'::text)
1676 -> Seq Scan on part_ab_cd
1677 Filter: ((a)::text = 'ab'::text)
1680 create table range_list_parted (
1683 ) partition by range (a);
1684 create table part_1_10 partition of range_list_parted for values from (1) to (10) partition by list (b);
1685 create table part_1_10_ab partition of part_1_10 for values in ('ab');
1686 create table part_1_10_cd partition of part_1_10 for values in ('cd');
1687 create table part_10_20 partition of range_list_parted for values from (10) to (20) partition by list (b);
1688 create table part_10_20_ab partition of part_10_20 for values in ('ab');
1689 create table part_10_20_cd partition of part_10_20 for values in ('cd');
1690 create table part_21_30 partition of range_list_parted for values from (21) to (30) partition by list (b);
1691 create table part_21_30_ab partition of part_21_30 for values in ('ab');
1692 create table part_21_30_cd partition of part_21_30 for values in ('cd');
1693 create table part_40_inf partition of range_list_parted for values from (40) to (unbounded) partition by list (b);
1694 create table part_40_inf_ab partition of part_40_inf for values in ('ab');
1695 create table part_40_inf_cd partition of part_40_inf for values in ('cd');
1696 create table part_40_inf_null partition of part_40_inf for values in (null);
1697 explain (costs off) select * from range_list_parted;
1699 -------------------------------------
1701 -> Seq Scan on range_list_parted
1702 -> Seq Scan on part_1_10
1703 -> Seq Scan on part_10_20
1704 -> Seq Scan on part_21_30
1705 -> Seq Scan on part_40_inf
1706 -> Seq Scan on part_1_10_ab
1707 -> Seq Scan on part_1_10_cd
1708 -> Seq Scan on part_10_20_ab
1709 -> Seq Scan on part_10_20_cd
1710 -> Seq Scan on part_21_30_ab
1711 -> Seq Scan on part_21_30_cd
1712 -> Seq Scan on part_40_inf_ab
1713 -> Seq Scan on part_40_inf_cd
1714 -> Seq Scan on part_40_inf_null
1717 explain (costs off) select * from range_list_parted where a = 5;
1719 -------------------------------------
1721 -> Seq Scan on range_list_parted
1723 -> Seq Scan on part_1_10
1725 -> Seq Scan on part_1_10_ab
1727 -> Seq Scan on part_1_10_cd
1731 explain (costs off) select * from range_list_parted where b = 'ab';
1733 -------------------------------------
1735 -> Seq Scan on range_list_parted
1736 Filter: (b = 'ab'::bpchar)
1737 -> Seq Scan on part_1_10
1738 Filter: (b = 'ab'::bpchar)
1739 -> Seq Scan on part_10_20
1740 Filter: (b = 'ab'::bpchar)
1741 -> Seq Scan on part_21_30
1742 Filter: (b = 'ab'::bpchar)
1743 -> Seq Scan on part_40_inf
1744 Filter: (b = 'ab'::bpchar)
1745 -> Seq Scan on part_1_10_ab
1746 Filter: (b = 'ab'::bpchar)
1747 -> Seq Scan on part_10_20_ab
1748 Filter: (b = 'ab'::bpchar)
1749 -> Seq Scan on part_21_30_ab
1750 Filter: (b = 'ab'::bpchar)
1751 -> Seq Scan on part_40_inf_ab
1752 Filter: (b = 'ab'::bpchar)
1755 explain (costs off) select * from range_list_parted where a between 3 and 23 and b in ('ab');
1757 -----------------------------------------------------------------
1759 -> Seq Scan on range_list_parted
1760 Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
1761 -> Seq Scan on part_1_10
1762 Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
1763 -> Seq Scan on part_10_20
1764 Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
1765 -> Seq Scan on part_21_30
1766 Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
1767 -> Seq Scan on part_1_10_ab
1768 Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
1769 -> Seq Scan on part_10_20_ab
1770 Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
1771 -> Seq Scan on part_21_30_ab
1772 Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar))
1775 /* Should select no rows because range partition key cannot be null */
1776 explain (costs off) select * from range_list_parted where a is null;
1778 --------------------------
1780 One-Time Filter: false
1783 /* Should only select rows from the null-accepting partition */
1784 explain (costs off) select * from range_list_parted where b is null;
1786 -------------------------------------
1788 -> Seq Scan on range_list_parted
1790 -> Seq Scan on part_1_10
1792 -> Seq Scan on part_10_20
1794 -> Seq Scan on part_21_30
1796 -> Seq Scan on part_40_inf
1798 -> Seq Scan on part_40_inf_null
1802 explain (costs off) select * from range_list_parted where a is not null and a < 67;
1804 ------------------------------------------------
1806 -> Seq Scan on range_list_parted
1807 Filter: ((a IS NOT NULL) AND (a < 67))
1808 -> Seq Scan on part_1_10
1809 Filter: ((a IS NOT NULL) AND (a < 67))
1810 -> Seq Scan on part_10_20
1811 Filter: ((a IS NOT NULL) AND (a < 67))
1812 -> Seq Scan on part_21_30
1813 Filter: ((a IS NOT NULL) AND (a < 67))
1814 -> Seq Scan on part_40_inf
1815 Filter: ((a IS NOT NULL) AND (a < 67))
1816 -> Seq Scan on part_1_10_ab
1817 Filter: ((a IS NOT NULL) AND (a < 67))
1818 -> Seq Scan on part_1_10_cd
1819 Filter: ((a IS NOT NULL) AND (a < 67))
1820 -> Seq Scan on part_10_20_ab
1821 Filter: ((a IS NOT NULL) AND (a < 67))
1822 -> Seq Scan on part_10_20_cd
1823 Filter: ((a IS NOT NULL) AND (a < 67))
1824 -> Seq Scan on part_21_30_ab
1825 Filter: ((a IS NOT NULL) AND (a < 67))
1826 -> Seq Scan on part_21_30_cd
1827 Filter: ((a IS NOT NULL) AND (a < 67))
1828 -> Seq Scan on part_40_inf_ab
1829 Filter: ((a IS NOT NULL) AND (a < 67))
1830 -> Seq Scan on part_40_inf_cd
1831 Filter: ((a IS NOT NULL) AND (a < 67))
1832 -> Seq Scan on part_40_inf_null
1833 Filter: ((a IS NOT NULL) AND (a < 67))
1836 explain (costs off) select * from range_list_parted where a >= 30;
1838 -------------------------------------
1840 -> Seq Scan on range_list_parted
1842 -> Seq Scan on part_40_inf
1844 -> Seq Scan on part_40_inf_ab
1846 -> Seq Scan on part_40_inf_cd
1848 -> Seq Scan on part_40_inf_null
1852 drop table list_parted;
1853 drop table range_list_parted;
projects / postgresql / blob