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 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "z_pkey" for table "z"
540 INSERT INTO z VALUES (NULL, 'text'); -- should fail
541 ERROR: null value in column "aa" violates not-null constraint
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 relname, bar.* FROM bar, pg_class where bar.tableoid = pg_class.oid
574 /* Test multiple inheritance of column defaults */
575 CREATE TABLE firstparent (tomorrow date default now()::date + 1);
576 CREATE TABLE secondparent (tomorrow date default now() :: date + 1);
577 CREATE TABLE jointchild () INHERITS (firstparent, secondparent); -- ok
578 NOTICE: merging multiple inherited definitions of column "tomorrow"
579 CREATE TABLE thirdparent (tomorrow date default now()::date - 1);
580 CREATE TABLE otherchild () INHERITS (firstparent, thirdparent); -- not ok
581 NOTICE: merging multiple inherited definitions of column "tomorrow"
582 ERROR: column "tomorrow" inherits conflicting default values
583 HINT: To resolve the conflict, specify a default explicitly.
584 CREATE TABLE otherchild (tomorrow date default now())
585 INHERITS (firstparent, thirdparent); -- ok, child resolves ambiguous default
586 NOTICE: merging multiple inherited definitions of column "tomorrow"
587 NOTICE: merging column "tomorrow" with inherited definition
588 DROP TABLE firstparent, secondparent, jointchild, thirdparent, otherchild;
589 /* Test inheritance of structure (LIKE) */
590 CREATE TABLE inhx (xx text DEFAULT 'text');
592 * Test double inheritance
594 * Ensure that defaults are NOT included unless
595 * INCLUDING DEFAULTS is specified
597 CREATE TABLE inhe (ee text, LIKE inhx) inherits (b);
598 INSERT INTO inhe VALUES ('ee-col1', 'ee-col2', DEFAULT, 'ee-col4');
599 SELECT * FROM inhe; /* Columns aa, bb, xx value NULL, ee */
601 ---------+---------+----+---------
602 ee-col1 | ee-col2 | | ee-col4
605 SELECT * FROM inhx; /* Empty set since LIKE inherits structure only */
610 SELECT * FROM b; /* Has ee entry */
616 SELECT * FROM a; /* Has ee entry */
622 CREATE TABLE inhf (LIKE inhx, LIKE inhx); /* Throw error */
623 ERROR: column "xx" specified more than once
624 CREATE TABLE inhf (LIKE inhx INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
625 INSERT INTO inhf DEFAULT VALUES;
626 SELECT * FROM inhf; /* Single entry with value 'text' */
632 ALTER TABLE inhx add constraint foo CHECK (xx = 'text');
633 ALTER TABLE inhx ADD PRIMARY KEY (xx);
634 NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "inhx_pkey" for table "inhx"
635 CREATE TABLE inhg (LIKE inhx); /* Doesn't copy constraint */
636 INSERT INTO inhg VALUES ('foo');
638 CREATE TABLE inhg (x text, LIKE inhx INCLUDING CONSTRAINTS, y text); /* Copies constraints */
639 INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds */
640 INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds -- Unique constraints not copied */
641 INSERT INTO inhg VALUES ('x', 'foo', 'y'); /* fails due to constraint */
642 ERROR: new row for relation "inhg" violates check constraint "foo"
643 SELECT * FROM inhg; /* Two records with three columns in order x=x, xx=text, y=y */
651 CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */
652 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "inhg_pkey" for table "inhg"
653 INSERT INTO inhg VALUES (5, 10);
654 INSERT INTO inhg VALUES (20, 10); -- should fail
655 ERROR: duplicate key value violates unique constraint "inhg_pkey"
656 DETAIL: Key (xx)=(10) already exists.
658 /* Multiple primary keys creation should fail */
659 CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, PRIMARY KEY(x)); /* fails */
660 ERROR: multiple primary keys for table "inhg" are not allowed
661 CREATE TABLE inhz (xx text DEFAULT 'text', yy int UNIQUE);
662 NOTICE: CREATE TABLE / UNIQUE will create implicit index "inhz_yy_key" for table "inhz"
663 CREATE UNIQUE INDEX inhz_xx_idx on inhz (xx) WHERE xx <> 'test';
664 /* Ok to create multiple unique indexes */
665 CREATE TABLE inhg (x text UNIQUE, LIKE inhz INCLUDING INDEXES);
666 NOTICE: CREATE TABLE / UNIQUE will create implicit index "inhg_x_key" for table "inhg"
667 NOTICE: CREATE TABLE / UNIQUE will create implicit index "inhg_yy_key" for table "inhg"
668 INSERT INTO inhg (xx, yy, x) VALUES ('test', 5, 10);
669 INSERT INTO inhg (xx, yy, x) VALUES ('test', 10, 15);
670 INSERT INTO inhg (xx, yy, x) VALUES ('foo', 10, 15); -- should fail
671 ERROR: duplicate key value violates unique constraint "inhg_x_key"
672 DETAIL: Key (x)=(15) already exists.
675 -- Test changing the type of inherited columns
676 insert into d values('test','one','two','three');
677 alter table a alter column aa type integer using bit_length(aa);
680 ----+-----+-----+-------
681 32 | one | two | three
684 -- Tests for casting between the rowtypes of parent and child
685 -- tables. See the pgsql-hackers thread beginning Dec. 4/04
686 create table base (i integer);
687 create table derived () inherits (base);
688 insert into derived (i) values (0);
689 select derived::base from derived;
697 create table p1(ff1 int);
698 create table p2(f1 text);
699 create function p2text(p2) returns text as 'select $1.f1' language sql;
700 create table c1(f3 int) inherits(p1,p2);
701 insert into c1 values(123456789, 'hi', 42);
702 select p2text(c1.*) from c1;
708 drop function p2text(p2);
712 CREATE TABLE ac (aa TEXT);
713 alter table ac add constraint ac_check check (aa is not null);
714 CREATE TABLE bc (bb TEXT) INHERITS (ac);
715 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;
716 relname | conname | contype | conislocal | coninhcount | consrc
717 ---------+----------+---------+------------+-------------+------------------
718 ac | ac_check | c | t | 0 | (aa IS NOT NULL)
719 bc | ac_check | c | f | 1 | (aa IS NOT NULL)
722 insert into ac (aa) values (NULL);
723 ERROR: new row for relation "ac" violates check constraint "ac_check"
724 insert into bc (aa) values (NULL);
725 ERROR: new row for relation "bc" violates check constraint "ac_check"
726 alter table bc drop constraint ac_check; -- fail, disallowed
727 ERROR: cannot drop inherited constraint "ac_check" of relation "bc"
728 alter table ac drop constraint ac_check;
729 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;
730 relname | conname | contype | conislocal | coninhcount | consrc
731 ---------+---------+---------+------------+-------------+--------
734 -- try the unnamed-constraint case
735 alter table ac add check (aa is not null);
736 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;
737 relname | conname | contype | conislocal | coninhcount | consrc
738 ---------+-------------+---------+------------+-------------+------------------
739 ac | ac_aa_check | c | t | 0 | (aa IS NOT NULL)
740 bc | ac_aa_check | c | f | 1 | (aa IS NOT NULL)
743 insert into ac (aa) values (NULL);
744 ERROR: new row for relation "ac" violates check constraint "ac_aa_check"
745 insert into bc (aa) values (NULL);
746 ERROR: new row for relation "bc" violates check constraint "ac_aa_check"
747 alter table bc drop constraint ac_aa_check; -- fail, disallowed
748 ERROR: cannot drop inherited constraint "ac_aa_check" of relation "bc"
749 alter table ac drop constraint ac_aa_check;
750 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;
751 relname | conname | contype | conislocal | coninhcount | consrc
752 ---------+---------+---------+------------+-------------+--------
755 alter table ac add constraint ac_check check (aa is not null);
756 alter table bc no inherit ac;
757 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;
758 relname | conname | contype | conislocal | coninhcount | consrc
759 ---------+----------+---------+------------+-------------+------------------
760 ac | ac_check | c | t | 0 | (aa IS NOT NULL)
761 bc | ac_check | c | t | 0 | (aa IS NOT NULL)
764 alter table bc drop constraint ac_check;
765 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;
766 relname | conname | contype | conislocal | coninhcount | consrc
767 ---------+----------+---------+------------+-------------+------------------
768 ac | ac_check | c | t | 0 | (aa IS NOT NULL)
771 alter table ac drop constraint ac_check;
772 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;
773 relname | conname | contype | conislocal | coninhcount | consrc
774 ---------+---------+---------+------------+-------------+--------
779 create table ac (a int constraint check_a check (a <> 0));
780 create table bc (a int constraint check_a check (a <> 0), b int constraint check_b check (b <> 0)) inherits (ac);
781 NOTICE: merging column "a" with inherited definition
782 NOTICE: merging constraint "check_a" with inherited definition
783 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;
784 relname | conname | contype | conislocal | coninhcount | consrc
785 ---------+---------+---------+------------+-------------+----------
786 ac | check_a | c | t | 0 | (a <> 0)
787 bc | check_a | c | t | 1 | (a <> 0)
788 bc | check_b | c | t | 0 | (b <> 0)
793 create table ac (a int constraint check_a check (a <> 0));
794 create table bc (b int constraint check_b check (b <> 0));
795 create table cc (c int constraint check_c check (c <> 0)) inherits (ac, bc);
796 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;
797 relname | conname | contype | conislocal | coninhcount | consrc
798 ---------+---------+---------+------------+-------------+----------
799 ac | check_a | c | t | 0 | (a <> 0)
800 bc | check_b | c | t | 0 | (b <> 0)
801 cc | check_a | c | f | 1 | (a <> 0)
802 cc | check_b | c | f | 1 | (b <> 0)
803 cc | check_c | c | t | 0 | (c <> 0)
806 alter table cc no inherit bc;
807 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;
808 relname | conname | contype | conislocal | coninhcount | consrc
809 ---------+---------+---------+------------+-------------+----------
810 ac | check_a | c | t | 0 | (a <> 0)
811 bc | check_b | c | t | 0 | (b <> 0)
812 cc | check_a | c | f | 1 | (a <> 0)
813 cc | check_b | c | t | 0 | (b <> 0)
814 cc | check_c | c | t | 0 | (c <> 0)
820 create table p1(f1 int);
821 create table p2(f2 int);
822 create table c1(f3 int) inherits(p1,p2);
823 insert into c1 values(1,-1,2);
824 alter table p2 add constraint cc check (f2>0); -- fail
825 ERROR: check constraint "cc" is violated by some row
826 alter table p2 add check (f2>0); -- check it without a name, too
827 ERROR: check constraint "p2_f2_check" is violated by some row
829 insert into c1 values(1,1,2);
830 alter table p2 add check (f2>0);
831 insert into c1 values(1,-1,2); -- fail
832 ERROR: new row for relation "c1" violates check constraint "p2_f2_check"
833 create table c2(f3 int) inherits(p1,p2);
836 Column | Type | Modifiers
837 --------+---------+-----------
842 "p2_f2_check" CHECK (f2 > 0)
846 create table c3 (f4 int) inherits(c1,c2);
847 NOTICE: merging multiple inherited definitions of column "f1"
848 NOTICE: merging multiple inherited definitions of column "f2"
849 NOTICE: merging multiple inherited definitions of column "f3"
852 Column | Type | Modifiers
853 --------+---------+-----------
859 "p2_f2_check" CHECK (f2 > 0)
863 drop table p1 cascade;
864 NOTICE: drop cascades to 3 other objects
865 DETAIL: drop cascades to table c1
866 drop cascades to table c2
867 drop cascades to table c3
868 drop table p2 cascade;
869 create table pp1 (f1 int);
870 create table cc1 (f2 text, f3 int) inherits (pp1);
871 alter table pp1 add column a1 int check (a1 > 0);
874 Column | Type | Modifiers
875 --------+---------+-----------
881 "pp1_a1_check" CHECK (a1 > 0)
884 create table cc2(f4 float) inherits(pp1,cc1);
885 NOTICE: merging multiple inherited definitions of column "f1"
886 NOTICE: merging multiple inherited definitions of column "a1"
889 Column | Type | Modifiers
890 --------+------------------+-----------
895 f4 | double precision |
897 "pp1_a1_check" CHECK (a1 > 0)
901 alter table pp1 add column a2 int check (a2 > 0);
902 NOTICE: merging definition of column "a2" for child "cc2"
903 NOTICE: merging constraint "pp1_a2_check" with inherited definition
906 Column | Type | Modifiers
907 --------+------------------+-----------
912 f4 | double precision |
915 "pp1_a1_check" CHECK (a1 > 0)
916 "pp1_a2_check" CHECK (a2 > 0)
920 drop table pp1 cascade;
921 NOTICE: drop cascades to 2 other objects
922 DETAIL: drop cascades to table cc1
923 drop cascades to table cc2
924 -- including storage and comments
925 CREATE TABLE t1 (a text CHECK (length(a) > 2) PRIMARY KEY, b text);
926 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
927 CREATE INDEX t1_b_key ON t1 (b);
928 CREATE INDEX t1_fnidx ON t1 ((a || b));
929 COMMENT ON COLUMN t1.a IS 'A';
930 COMMENT ON COLUMN t1.b IS 'B';
931 COMMENT ON CONSTRAINT t1_a_check ON t1 IS 't1_a_check';
932 COMMENT ON INDEX t1_pkey IS 'index pkey';
933 COMMENT ON INDEX t1_b_key IS 'index b_key';
934 ALTER TABLE t1 ALTER COLUMN a SET STORAGE MAIN;
935 CREATE TABLE t2 (c text);
936 ALTER TABLE t2 ALTER COLUMN c SET STORAGE EXTERNAL;
937 COMMENT ON COLUMN t2.c IS 'C';
938 CREATE TABLE t3 (a text CHECK (length(a) < 5), c text);
939 ALTER TABLE t3 ALTER COLUMN c SET STORAGE EXTERNAL;
940 ALTER TABLE t3 ALTER COLUMN a SET STORAGE MAIN;
941 COMMENT ON COLUMN t3.a IS 'A3';
942 COMMENT ON COLUMN t3.c IS 'C';
943 COMMENT ON CONSTRAINT t3_a_check ON t3 IS 't3_a_check';
944 CREATE TABLE t4 (a text, c text);
945 ALTER TABLE t4 ALTER COLUMN c SET STORAGE EXTERNAL;
946 CREATE TABLE t12_storage (LIKE t1 INCLUDING STORAGE, LIKE t2 INCLUDING STORAGE);
948 Table "public.t12_storage"
949 Column | Type | Modifiers | Storage | Description
950 --------+------+-----------+----------+-------------
951 a | text | not null | main |
952 b | text | | extended |
953 c | text | | external |
956 CREATE TABLE t12_comments (LIKE t1 INCLUDING COMMENTS, LIKE t2 INCLUDING COMMENTS);
958 Table "public.t12_comments"
959 Column | Type | Modifiers | Storage | Description
960 --------+------+-----------+----------+-------------
961 a | text | not null | extended | A
962 b | text | | extended | B
963 c | text | | extended | C
966 CREATE TABLE t1_inh (LIKE t1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INHERITS (t1);
967 NOTICE: merging column "a" with inherited definition
968 NOTICE: merging column "b" with inherited definition
969 NOTICE: merging constraint "t1_a_check" with inherited definition
971 Table "public.t1_inh"
972 Column | Type | Modifiers | Storage | Description
973 --------+------+-----------+----------+-------------
974 a | text | not null | main | A
975 b | text | | extended | B
977 "t1_a_check" CHECK (length(a) > 2)
981 SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 't1_inh'::regclass;
987 CREATE TABLE t13_inh () INHERITS (t1, t3);
988 NOTICE: merging multiple inherited definitions of column "a"
990 Table "public.t13_inh"
991 Column | Type | Modifiers | Storage | Description
992 --------+------+-----------+----------+-------------
993 a | text | not null | main |
994 b | text | | extended |
995 c | text | | external |
997 "t1_a_check" CHECK (length(a) > 2)
998 "t3_a_check" CHECK (length(a) < 5)
1003 CREATE TABLE t13_like (LIKE t3 INCLUDING CONSTRAINTS INCLUDING COMMENTS INCLUDING STORAGE) INHERITS (t1);
1004 NOTICE: merging column "a" with inherited definition
1006 Table "public.t13_like"
1007 Column | Type | Modifiers | Storage | Description
1008 --------+------+-----------+----------+-------------
1009 a | text | not null | main | A3
1010 b | text | | extended |
1011 c | text | | external | C
1013 "t1_a_check" CHECK (length(a) > 2)
1014 "t3_a_check" CHECK (length(a) < 5)
1018 SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 't13_like'::regclass;
1024 CREATE TABLE t_all (LIKE t1 INCLUDING ALL);
1025 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t_all_pkey" for table "t_all"
1027 Table "public.t_all"
1028 Column | Type | Modifiers | Storage | Description
1029 --------+------+-----------+----------+-------------
1030 a | text | not null | main | A
1031 b | text | | extended | B
1033 "t_all_pkey" PRIMARY KEY, btree (a)
1034 "t_all_b_idx" btree (b)
1035 "t_all_expr_idx" btree ((a || b))
1037 "t1_a_check" CHECK (length(a) > 2)
1040 SELECT c.relname, objsubid, description FROM pg_description, pg_index i, pg_class c WHERE classoid = 'pg_class'::regclass AND objoid = i.indexrelid AND c.oid = i.indexrelid AND i.indrelid = 't_all'::regclass ORDER BY c.relname, objsubid;
1041 relname | objsubid | description
1042 -------------+----------+-------------
1043 t_all_b_idx | 0 | index b_key
1044 t_all_pkey | 0 | index pkey
1047 CREATE TABLE inh_error1 () INHERITS (t1, t4);
1048 NOTICE: merging multiple inherited definitions of column "a"
1049 ERROR: inherited column "a" has a storage parameter conflict
1050 DETAIL: MAIN versus EXTENDED
1051 CREATE TABLE inh_error2 (LIKE t4 INCLUDING STORAGE) INHERITS (t1);
1052 NOTICE: merging column "a" with inherited definition
1053 ERROR: column "a" has a storage parameter conflict
1054 DETAIL: MAIN versus EXTENDED
1055 DROP TABLE t1, t2, t3, t4, t12_storage, t12_comments, t1_inh, t13_inh, t13_like, t_all;
1056 -- Test for renaming in simple multiple inheritance
1057 CREATE TABLE t1 (a int, b int);
1058 CREATE TABLE s1 (b int, c int);
1059 CREATE TABLE ts (d int) INHERITS (t1, s1);
1060 NOTICE: merging multiple inherited definitions of column "b"
1061 ALTER TABLE t1 RENAME a TO aa;
1062 ALTER TABLE t1 RENAME b TO bb; -- to be failed
1063 ERROR: cannot rename inherited column "b"
1064 ALTER TABLE ts RENAME aa TO aaa; -- to be failed
1065 ERROR: cannot rename inherited column "aa"
1066 ALTER TABLE ts RENAME d TO dd;
1069 Column | Type | Modifiers | Storage | Description
1070 --------+---------+-----------+---------+-------------
1071 aa | integer | | plain |
1072 b | integer | | plain |
1073 c | integer | | plain |
1074 dd | integer | | plain |
1080 -- Test for renaming in diamond inheritance
1081 CREATE TABLE t2 (x int) INHERITS (t1);
1082 CREATE TABLE t3 (y int) INHERITS (t1);
1083 CREATE TABLE t4 (z int) INHERITS (t2, t3);
1084 NOTICE: merging multiple inherited definitions of column "aa"
1085 NOTICE: merging multiple inherited definitions of column "b"
1086 ALTER TABLE t1 RENAME aa TO aaa;
1089 Column | Type | Modifiers | Storage | Description
1090 --------+---------+-----------+---------+-------------
1091 aaa | integer | | plain |
1092 b | integer | | plain |
1093 x | integer | | plain |
1094 y | integer | | plain |
1095 z | integer | | plain |
1100 CREATE TABLE ts (d int) INHERITS (t2, s1);
1101 NOTICE: merging multiple inherited definitions of column "b"
1102 ALTER TABLE t1 RENAME aaa TO aaaa;
1103 ALTER TABLE t1 RENAME b TO bb; -- to be failed
1104 ERROR: cannot rename inherited column "b"
1107 Column | Type | Modifiers | Storage | Description
1108 --------+---------+-----------+---------+-------------
1109 aaaa | integer | | plain |
1110 b | integer | | plain |
1111 x | integer | | plain |
1112 c | integer | | plain |
1113 d | integer | | plain |
1118 WITH RECURSIVE r AS (
1119 SELECT 't1'::regclass AS inhrelid
1121 SELECT c.inhrelid FROM pg_inherits c, r WHERE r.inhrelid = c.inhparent
1123 SELECT a.attrelid::regclass, a.attname, a.attinhcount, e.expected
1124 FROM (SELECT inhrelid, count(*) AS expected FROM pg_inherits
1125 WHERE inhparent IN (SELECT inhrelid FROM r) GROUP BY inhrelid) e
1126 JOIN pg_attribute a ON e.inhrelid = a.attrelid WHERE NOT attislocal
1127 ORDER BY a.attrelid::regclass::name, a.attnum;
1128 attrelid | attname | attinhcount | expected
1129 ----------+---------+-------------+----------
1144 DROP TABLE t1, s1 CASCADE;
1145 NOTICE: drop cascades to 4 other objects
1146 DETAIL: drop cascades to table t2
1147 drop cascades to table ts
1148 drop cascades to table t3
1149 drop cascades to table t4
1151 -- Test merge-append plans for inheritance trees
1153 create table matest0 (id serial primary key, name text);
1154 NOTICE: CREATE TABLE will create implicit sequence "matest0_id_seq" for serial column "matest0.id"
1155 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "matest0_pkey" for table "matest0"
1156 create table matest1 (id integer primary key) inherits (matest0);
1157 NOTICE: merging column "id" with inherited definition
1158 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "matest1_pkey" for table "matest1"
1159 create table matest2 (id integer primary key) inherits (matest0);
1160 NOTICE: merging column "id" with inherited definition
1161 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "matest2_pkey" for table "matest2"
1162 create table matest3 (id integer primary key) inherits (matest0);
1163 NOTICE: merging column "id" with inherited definition
1164 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "matest3_pkey" for table "matest3"
1165 create index matest0i on matest0 ((1-id));
1166 create index matest1i on matest1 ((1-id));
1167 -- create index matest2i on matest2 ((1-id)); -- intentionally missing
1168 create index matest3i on matest3 ((1-id));
1169 insert into matest1 (name) values ('Test 1');
1170 insert into matest1 (name) values ('Test 2');
1171 insert into matest2 (name) values ('Test 3');
1172 insert into matest2 (name) values ('Test 4');
1173 insert into matest3 (name) values ('Test 5');
1174 insert into matest3 (name) values ('Test 6');
1175 set enable_indexscan = off; -- force use of seqscan/sort, so no merge
1176 explain (verbose, costs off) select * from matest0 order by 1-id;
1178 ---------------------------------------------------------------------------------
1180 Output: public.matest0.id, public.matest0.name, ((1 - public.matest0.id))
1181 Sort Key: ((1 - public.matest0.id))
1183 Output: public.matest0.id, public.matest0.name, (1 - public.matest0.id)
1185 -> Seq Scan on public.matest0
1186 Output: public.matest0.id, public.matest0.name
1187 -> Seq Scan on public.matest1 matest0
1188 Output: public.matest0.id, public.matest0.name
1189 -> Seq Scan on public.matest2 matest0
1190 Output: public.matest0.id, public.matest0.name
1191 -> Seq Scan on public.matest3 matest0
1192 Output: public.matest0.id, public.matest0.name
1195 select * from matest0 order by 1-id;
1206 reset enable_indexscan;
1207 set enable_seqscan = off; -- plan with fewest seqscans should be merge
1208 explain (verbose, costs off) select * from matest0 order by 1-id;
1210 ---------------------------------------------------------------------------------------------
1212 Output: public.matest0.id, public.matest0.name, ((1 - public.matest0.id))
1214 Sort Key: ((1 - public.matest0.id))
1215 -> Index Scan using matest0i on public.matest0
1216 Output: public.matest0.id, public.matest0.name, (1 - public.matest0.id)
1217 -> Index Scan using matest1i on public.matest1 matest0
1218 Output: public.matest0.id, public.matest0.name, (1 - public.matest0.id)
1220 Output: public.matest0.id, public.matest0.name, ((1 - public.matest0.id))
1221 Sort Key: ((1 - public.matest0.id))
1222 -> Seq Scan on public.matest2 matest0
1223 Output: public.matest0.id, public.matest0.name, (1 - public.matest0.id)
1224 -> Index Scan using matest3i on public.matest3 matest0
1225 Output: public.matest0.id, public.matest0.name, (1 - public.matest0.id)
1228 select * from matest0 order by 1-id;
1239 reset enable_seqscan;
1240 drop table matest0 cascade;
1241 NOTICE: drop cascades to 3 other objects
1242 DETAIL: drop cascades to table matest1
1243 drop cascades to table matest2
1244 drop cascades to table matest3
projects / postgresql / blob