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 68CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */ 69INSERT INTO inhg VALUES (5, 10); 70INSERT INTO inhg VALUES (20, 10); -- should fail 71DROP TABLE inhg; 72/* Multiple primary keys creation should fail */ 73CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, PRIMARY KEY(x)); /* fails */ 74CREATE TABLE inhz (xx text DEFAULT 'text', yy int UNIQUE); 75CREATE UNIQUE INDEX inhz_xx_idx on inhz (xx) WHERE xx <> 'test'; 76/* Ok to create multiple unique indexes */ 77CREATE TABLE inhg (x text UNIQUE, LIKE inhz INCLUDING INDEXES); 78INSERT INTO inhg (xx, yy, x) VALUES ('test', 5, 10); 79INSERT INTO inhg (xx, yy, x) VALUES ('test', 10, 15); 80INSERT INTO inhg (xx, yy, x) VALUES ('foo', 10, 15); -- should fail 81DROP TABLE inhg; 82DROP TABLE inhz; 83 84-- including storage and comments 85CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) PRIMARY KEY, b text); 86CREATE INDEX ctlt1_b_key ON ctlt1 (b); 87CREATE INDEX ctlt1_fnidx ON ctlt1 ((a || b)); 88CREATE STATISTICS ctlt1_a_b_stat ON a,b FROM ctlt1; 89COMMENT ON STATISTICS ctlt1_a_b_stat IS 'ab stats'; 90COMMENT ON COLUMN ctlt1.a IS 'A'; 91COMMENT ON COLUMN ctlt1.b IS 'B'; 92COMMENT ON CONSTRAINT ctlt1_a_check ON ctlt1 IS 't1_a_check'; 93COMMENT ON INDEX ctlt1_pkey IS 'index pkey'; 94COMMENT ON INDEX ctlt1_b_key IS 'index b_key'; 95ALTER TABLE ctlt1 ALTER COLUMN a SET STORAGE MAIN; 96 97CREATE TABLE ctlt2 (c text); 98ALTER TABLE ctlt2 ALTER COLUMN c SET STORAGE EXTERNAL; 99COMMENT ON COLUMN ctlt2.c IS 'C'; 100 101CREATE TABLE ctlt3 (a text CHECK (length(a) < 5), c text); 102ALTER TABLE ctlt3 ALTER COLUMN c SET STORAGE EXTERNAL; 103ALTER TABLE ctlt3 ALTER COLUMN a SET STORAGE MAIN; 104COMMENT ON COLUMN ctlt3.a IS 'A3'; 105COMMENT ON COLUMN ctlt3.c IS 'C'; 106COMMENT ON CONSTRAINT ctlt3_a_check ON ctlt3 IS 't3_a_check'; 107 108CREATE TABLE ctlt4 (a text, c text); 109ALTER TABLE ctlt4 ALTER COLUMN c SET STORAGE EXTERNAL; 110 111CREATE TABLE ctlt12_storage (LIKE ctlt1 INCLUDING STORAGE, LIKE ctlt2 INCLUDING STORAGE); 112\d+ ctlt12_storage 113CREATE TABLE ctlt12_comments (LIKE ctlt1 INCLUDING COMMENTS, LIKE ctlt2 INCLUDING COMMENTS); 114\d+ ctlt12_comments 115CREATE TABLE ctlt1_inh (LIKE ctlt1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INHERITS (ctlt1); 116\d+ ctlt1_inh 117SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 'ctlt1_inh'::regclass; 118CREATE TABLE ctlt13_inh () INHERITS (ctlt1, ctlt3); 119\d+ ctlt13_inh 120CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING COMMENTS INCLUDING STORAGE) INHERITS (ctlt1); 121\d+ ctlt13_like 122SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 'ctlt13_like'::regclass; 123 124CREATE TABLE ctlt_all (LIKE ctlt1 INCLUDING ALL); 125\d+ ctlt_all 126SELECT 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; 127SELECT 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; 128 129CREATE TABLE inh_error1 () INHERITS (ctlt1, ctlt4); 130CREATE TABLE inh_error2 (LIKE ctlt4 INCLUDING STORAGE) INHERITS (ctlt1); 131 132DROP TABLE ctlt1, ctlt2, ctlt3, ctlt4, ctlt12_storage, ctlt12_comments, ctlt1_inh, ctlt13_inh, ctlt13_like, ctlt_all, ctla, ctlb CASCADE; 133 134 135/* LIKE with other relation kinds */ 136 137CREATE TABLE ctlt4 (a int, b text); 138 139CREATE SEQUENCE ctlseq1; 140CREATE TABLE ctlt10 (LIKE ctlseq1); -- fail 141 142CREATE VIEW ctlv1 AS SELECT * FROM ctlt4; 143CREATE TABLE ctlt11 (LIKE ctlv1); 144CREATE TABLE ctlt11a (LIKE ctlv1 INCLUDING ALL); 145 146CREATE TYPE ctlty1 AS (a int, b text); 147CREATE TABLE ctlt12 (LIKE ctlty1); 148 149DROP SEQUENCE ctlseq1; 150DROP TYPE ctlty1; 151DROP VIEW ctlv1; 152DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12; 153