1/* Test inheritance of structure (LIKE) */ 2CREATE TABLE inhx (xx text DEFAULT 'text'); 3 4/* 5 * Test double inheritance 6 * 7 * Ensure that defaults are NOT included unless 8 * INCLUDING DEFAULTS is specified 9 */ 10CREATE TABLE ctla (aa TEXT); 11CREATE TABLE ctlb (bb TEXT) INHERITS (ctla); 12 13CREATE TABLE foo (LIKE nonexistent); 14 15CREATE TABLE inhe (ee text, LIKE inhx) inherits (ctlb); 16INSERT INTO inhe VALUES ('ee-col1', 'ee-col2', DEFAULT, 'ee-col4'); 17SELECT * FROM inhe; /* Columns aa, bb, xx value NULL, ee */ 18SELECT * FROM inhx; /* Empty set since LIKE inherits structure only */ 19SELECT * FROM ctlb; /* Has ee entry */ 20SELECT * FROM ctla; /* Has ee entry */ 21 22CREATE TABLE inhf (LIKE inhx, LIKE inhx); /* Throw error */ 23 24CREATE TABLE inhf (LIKE inhx INCLUDING DEFAULTS INCLUDING CONSTRAINTS); 25INSERT INTO inhf DEFAULT VALUES; 26SELECT * FROM inhf; /* Single entry with value 'text' */ 27 28ALTER TABLE inhx add constraint foo CHECK (xx = 'text'); 29ALTER TABLE inhx ADD PRIMARY KEY (xx); 30CREATE TABLE inhg (LIKE inhx); /* Doesn't copy constraint */ 31INSERT INTO inhg VALUES ('foo'); 32DROP TABLE inhg; 33CREATE TABLE inhg (x text, LIKE inhx INCLUDING CONSTRAINTS, y text); /* Copies constraints */ 34INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds */ 35INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds -- Unique constraints not copied */ 36INSERT INTO inhg VALUES ('x', 'foo', 'y'); /* fails due to constraint */ 37SELECT * FROM inhg; /* Two records with three columns in order x=x, xx=text, y=y */ 38DROP TABLE inhg; 39 40CREATE TABLE test_like_id_1 (a bigint GENERATED ALWAYS AS IDENTITY, b text); 41\d test_like_id_1 42INSERT INTO test_like_id_1 (b) VALUES ('b1'); 43SELECT * FROM test_like_id_1; 44CREATE TABLE test_like_id_2 (LIKE test_like_id_1); 45\d test_like_id_2 46INSERT INTO test_like_id_2 (b) VALUES ('b2'); 47SELECT * FROM test_like_id_2; -- identity was not copied 48CREATE TABLE test_like_id_3 (LIKE test_like_id_1 INCLUDING IDENTITY); 49\d test_like_id_3 50INSERT INTO test_like_id_3 (b) VALUES ('b3'); 51SELECT * FROM test_like_id_3; -- identity was copied and applied 52DROP TABLE test_like_id_1, test_like_id_2, test_like_id_3; 53 54CREATE TABLE test_like_gen_1 (a int, b int GENERATED ALWAYS AS (a * 2) STORED); 55\d test_like_gen_1 56INSERT INTO test_like_gen_1 (a) VALUES (1); 57SELECT * FROM test_like_gen_1; 58CREATE TABLE test_like_gen_2 (LIKE test_like_gen_1); 59\d test_like_gen_2 60INSERT INTO test_like_gen_2 (a) VALUES (1); 61SELECT * FROM test_like_gen_2; 62CREATE TABLE test_like_gen_3 (LIKE test_like_gen_1 INCLUDING GENERATED); 63\d test_like_gen_3 64INSERT INTO test_like_gen_3 (a) VALUES (1); 65SELECT * FROM test_like_gen_3; 66DROP TABLE test_like_gen_1, test_like_gen_2, test_like_gen_3; 67 68-- also test generated column with a "forward" reference (bug #16342) 69CREATE TABLE test_like_4 (b int DEFAULT 42, 70 c int GENERATED ALWAYS AS (a * 2) STORED, 71 a int CHECK (a > 0)); 72\d test_like_4 73CREATE TABLE test_like_4a (LIKE test_like_4); 74CREATE TABLE test_like_4b (LIKE test_like_4 INCLUDING DEFAULTS); 75CREATE TABLE test_like_4c (LIKE test_like_4 INCLUDING GENERATED); 76CREATE TABLE test_like_4d (LIKE test_like_4 INCLUDING DEFAULTS INCLUDING GENERATED); 77\d test_like_4a 78INSERT INTO test_like_4a (a) VALUES(11); 79SELECT a, b, c FROM test_like_4a; 80\d test_like_4b 81INSERT INTO test_like_4b (a) VALUES(11); 82SELECT a, b, c FROM test_like_4b; 83\d test_like_4c 84INSERT INTO test_like_4c (a) VALUES(11); 85SELECT a, b, c FROM test_like_4c; 86\d test_like_4d 87INSERT INTO test_like_4d (a) VALUES(11); 88SELECT a, b, c FROM test_like_4d; 89 90-- Test renumbering of Vars when combining LIKE with inheritance 91CREATE TABLE test_like_5 (x point, y point, z point); 92CREATE TABLE test_like_5x (p int CHECK (p > 0), 93 q int GENERATED ALWAYS AS (p * 2) STORED); 94CREATE TABLE test_like_5c (LIKE test_like_4 INCLUDING ALL) 95 INHERITS (test_like_5, test_like_5x); 96\d test_like_5c 97 98DROP TABLE test_like_4, test_like_4a, test_like_4b, test_like_4c, test_like_4d; 99DROP TABLE test_like_5, test_like_5x, test_like_5c; 100 101CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */ 102INSERT INTO inhg VALUES (5, 10); 103INSERT INTO inhg VALUES (20, 10); -- should fail 104DROP TABLE inhg; 105/* Multiple primary keys creation should fail */ 106CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, PRIMARY KEY(x)); /* fails */ 107CREATE TABLE inhz (xx text DEFAULT 'text', yy int UNIQUE); 108CREATE UNIQUE INDEX inhz_xx_idx on inhz (xx) WHERE xx <> 'test'; 109/* Ok to create multiple unique indexes */ 110CREATE TABLE inhg (x text UNIQUE, LIKE inhz INCLUDING INDEXES); 111INSERT INTO inhg (xx, yy, x) VALUES ('test', 5, 10); 112INSERT INTO inhg (xx, yy, x) VALUES ('test', 10, 15); 113INSERT INTO inhg (xx, yy, x) VALUES ('foo', 10, 15); -- should fail 114DROP TABLE inhg; 115DROP TABLE inhz; 116 117/* Use primary key imported by LIKE for self-referential FK constraint */ 118CREATE TABLE inhz (x text REFERENCES inhz, LIKE inhx INCLUDING INDEXES); 119\d inhz 120DROP TABLE inhz; 121 122-- including storage and comments 123CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) PRIMARY KEY, b text); 124CREATE INDEX ctlt1_b_key ON ctlt1 (b); 125CREATE INDEX ctlt1_fnidx ON ctlt1 ((a || b)); 126CREATE STATISTICS ctlt1_a_b_stat ON a,b FROM ctlt1; 127CREATE STATISTICS ctlt1_expr_stat ON (a || b) FROM ctlt1; 128COMMENT ON STATISTICS ctlt1_a_b_stat IS 'ab stats'; 129COMMENT ON STATISTICS ctlt1_expr_stat IS 'ab expr stats'; 130COMMENT ON COLUMN ctlt1.a IS 'A'; 131COMMENT ON COLUMN ctlt1.b IS 'B'; 132COMMENT ON CONSTRAINT ctlt1_a_check ON ctlt1 IS 't1_a_check'; 133COMMENT ON INDEX ctlt1_pkey IS 'index pkey'; 134COMMENT ON INDEX ctlt1_b_key IS 'index b_key'; 135ALTER TABLE ctlt1 ALTER COLUMN a SET STORAGE MAIN; 136 137CREATE TABLE ctlt2 (c text); 138ALTER TABLE ctlt2 ALTER COLUMN c SET STORAGE EXTERNAL; 139COMMENT ON COLUMN ctlt2.c IS 'C'; 140 141CREATE TABLE ctlt3 (a text CHECK (length(a) < 5), c text CHECK (length(c) < 7)); 142ALTER TABLE ctlt3 ALTER COLUMN c SET STORAGE EXTERNAL; 143ALTER TABLE ctlt3 ALTER COLUMN a SET STORAGE MAIN; 144CREATE INDEX ctlt3_fnidx ON ctlt3 ((a || c)); 145COMMENT ON COLUMN ctlt3.a IS 'A3'; 146COMMENT ON COLUMN ctlt3.c IS 'C'; 147COMMENT ON CONSTRAINT ctlt3_a_check ON ctlt3 IS 't3_a_check'; 148 149CREATE TABLE ctlt4 (a text, c text); 150ALTER TABLE ctlt4 ALTER COLUMN c SET STORAGE EXTERNAL; 151 152CREATE TABLE ctlt12_storage (LIKE ctlt1 INCLUDING STORAGE, LIKE ctlt2 INCLUDING STORAGE); 153\d+ ctlt12_storage 154CREATE TABLE ctlt12_comments (LIKE ctlt1 INCLUDING COMMENTS, LIKE ctlt2 INCLUDING COMMENTS); 155\d+ ctlt12_comments 156CREATE TABLE ctlt1_inh (LIKE ctlt1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INHERITS (ctlt1); 157\d+ ctlt1_inh 158SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 'ctlt1_inh'::regclass; 159CREATE TABLE ctlt13_inh () INHERITS (ctlt1, ctlt3); 160\d+ ctlt13_inh 161CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING COMMENTS INCLUDING STORAGE) INHERITS (ctlt1); 162\d+ ctlt13_like 163SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 'ctlt13_like'::regclass; 164 165CREATE TABLE ctlt_all (LIKE ctlt1 INCLUDING ALL); 166\d+ ctlt_all 167SELECT 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 = 'ctlt_all'::regclass ORDER BY c.relname, objsubid; 168SELECT s.stxname, objsubid, description FROM pg_description, pg_statistic_ext s WHERE classoid = 'pg_statistic_ext'::regclass AND objoid = s.oid AND s.stxrelid = 'ctlt_all'::regclass ORDER BY s.stxname, objsubid; 169 170CREATE TABLE inh_error1 () INHERITS (ctlt1, ctlt4); 171CREATE TABLE inh_error2 (LIKE ctlt4 INCLUDING STORAGE) INHERITS (ctlt1); 172 173-- Check that LIKE isn't confused by a system catalog of the same name 174CREATE TABLE pg_attrdef (LIKE ctlt1 INCLUDING ALL); 175\d+ public.pg_attrdef 176DROP TABLE public.pg_attrdef; 177 178-- Check that LIKE isn't confused when new table masks the old, either 179BEGIN; 180CREATE SCHEMA ctl_schema; 181SET LOCAL search_path = ctl_schema, public; 182CREATE TABLE ctlt1 (LIKE ctlt1 INCLUDING ALL); 183\d+ ctlt1 184ROLLBACK; 185 186DROP TABLE ctlt1, ctlt2, ctlt3, ctlt4, ctlt12_storage, ctlt12_comments, ctlt1_inh, ctlt13_inh, ctlt13_like, ctlt_all, ctla, ctlb CASCADE; 187 188-- LIKE must respect NO INHERIT property of constraints 189CREATE TABLE noinh_con_copy (a int CHECK (a > 0) NO INHERIT); 190CREATE TABLE noinh_con_copy1 (LIKE noinh_con_copy INCLUDING CONSTRAINTS); 191\d noinh_con_copy1 192 193-- fail, as partitioned tables don't allow NO INHERIT constraints 194CREATE TABLE noinh_con_copy1_parted (LIKE noinh_con_copy INCLUDING ALL) 195 PARTITION BY LIST (a); 196 197DROP TABLE noinh_con_copy, noinh_con_copy1; 198 199 200/* LIKE with other relation kinds */ 201 202CREATE TABLE ctlt4 (a int, b text); 203 204CREATE SEQUENCE ctlseq1; 205CREATE TABLE ctlt10 (LIKE ctlseq1); -- fail 206 207CREATE VIEW ctlv1 AS SELECT * FROM ctlt4; 208CREATE TABLE ctlt11 (LIKE ctlv1); 209CREATE TABLE ctlt11a (LIKE ctlv1 INCLUDING ALL); 210 211CREATE TYPE ctlty1 AS (a int, b text); 212CREATE TABLE ctlt12 (LIKE ctlty1); 213 214DROP SEQUENCE ctlseq1; 215DROP TYPE ctlty1; 216DROP VIEW ctlv1; 217DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12; 218