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; 127COMMENT ON STATISTICS ctlt1_a_b_stat IS 'ab stats'; 128COMMENT ON COLUMN ctlt1.a IS 'A'; 129COMMENT ON COLUMN ctlt1.b IS 'B'; 130COMMENT ON CONSTRAINT ctlt1_a_check ON ctlt1 IS 't1_a_check'; 131COMMENT ON INDEX ctlt1_pkey IS 'index pkey'; 132COMMENT ON INDEX ctlt1_b_key IS 'index b_key'; 133ALTER TABLE ctlt1 ALTER COLUMN a SET STORAGE MAIN; 134 135CREATE TABLE ctlt2 (c text); 136ALTER TABLE ctlt2 ALTER COLUMN c SET STORAGE EXTERNAL; 137COMMENT ON COLUMN ctlt2.c IS 'C'; 138 139CREATE TABLE ctlt3 (a text CHECK (length(a) < 5), c text CHECK (length(c) < 7)); 140ALTER TABLE ctlt3 ALTER COLUMN c SET STORAGE EXTERNAL; 141ALTER TABLE ctlt3 ALTER COLUMN a SET STORAGE MAIN; 142CREATE INDEX ctlt3_fnidx ON ctlt3 ((a || c)); 143COMMENT ON COLUMN ctlt3.a IS 'A3'; 144COMMENT ON COLUMN ctlt3.c IS 'C'; 145COMMENT ON CONSTRAINT ctlt3_a_check ON ctlt3 IS 't3_a_check'; 146 147CREATE TABLE ctlt4 (a text, c text); 148ALTER TABLE ctlt4 ALTER COLUMN c SET STORAGE EXTERNAL; 149 150CREATE TABLE ctlt12_storage (LIKE ctlt1 INCLUDING STORAGE, LIKE ctlt2 INCLUDING STORAGE); 151\d+ ctlt12_storage 152CREATE TABLE ctlt12_comments (LIKE ctlt1 INCLUDING COMMENTS, LIKE ctlt2 INCLUDING COMMENTS); 153\d+ ctlt12_comments 154CREATE TABLE ctlt1_inh (LIKE ctlt1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INHERITS (ctlt1); 155\d+ ctlt1_inh 156SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 'ctlt1_inh'::regclass; 157CREATE TABLE ctlt13_inh () INHERITS (ctlt1, ctlt3); 158\d+ ctlt13_inh 159CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING COMMENTS INCLUDING STORAGE) INHERITS (ctlt1); 160\d+ ctlt13_like 161SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 'ctlt13_like'::regclass; 162 163CREATE TABLE ctlt_all (LIKE ctlt1 INCLUDING ALL); 164\d+ ctlt_all 165SELECT 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; 166SELECT 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; 167 168CREATE TABLE inh_error1 () INHERITS (ctlt1, ctlt4); 169CREATE TABLE inh_error2 (LIKE ctlt4 INCLUDING STORAGE) INHERITS (ctlt1); 170 171-- Check that LIKE isn't confused by a system catalog of the same name 172CREATE TABLE pg_attrdef (LIKE ctlt1 INCLUDING ALL); 173\d+ public.pg_attrdef 174DROP TABLE public.pg_attrdef; 175 176-- Check that LIKE isn't confused when new table masks the old, either 177BEGIN; 178CREATE SCHEMA ctl_schema; 179SET LOCAL search_path = ctl_schema, public; 180CREATE TABLE ctlt1 (LIKE ctlt1 INCLUDING ALL); 181\d+ ctlt1 182ROLLBACK; 183 184DROP TABLE ctlt1, ctlt2, ctlt3, ctlt4, ctlt12_storage, ctlt12_comments, ctlt1_inh, ctlt13_inh, ctlt13_like, ctlt_all, ctla, ctlb CASCADE; 185 186-- LIKE must respect NO INHERIT property of constraints 187CREATE TABLE noinh_con_copy (a int CHECK (a > 0) NO INHERIT); 188CREATE TABLE noinh_con_copy1 (LIKE noinh_con_copy INCLUDING CONSTRAINTS); 189\d noinh_con_copy1 190 191-- fail, as partitioned tables don't allow NO INHERIT constraints 192CREATE TABLE noinh_con_copy1_parted (LIKE noinh_con_copy INCLUDING ALL) 193 PARTITION BY LIST (a); 194 195DROP TABLE noinh_con_copy, noinh_con_copy1; 196 197 198/* LIKE with other relation kinds */ 199 200CREATE TABLE ctlt4 (a int, b text); 201 202CREATE SEQUENCE ctlseq1; 203CREATE TABLE ctlt10 (LIKE ctlseq1); -- fail 204 205CREATE VIEW ctlv1 AS SELECT * FROM ctlt4; 206CREATE TABLE ctlt11 (LIKE ctlv1); 207CREATE TABLE ctlt11a (LIKE ctlv1 INCLUDING ALL); 208 209CREATE TYPE ctlty1 AS (a int, b text); 210CREATE TABLE ctlt12 (LIKE ctlty1); 211 212DROP SEQUENCE ctlseq1; 213DROP TYPE ctlty1; 214DROP VIEW ctlv1; 215DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12; 216