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 40-- Test renumbering of Vars when combining LIKE with inheritance 41CREATE TABLE test_like_4 (b int DEFAULT 42, 42 c int NOT NULL, 43 a int CHECK (a > 0)); 44CREATE TABLE test_like_5 (x point, y point, z point); 45CREATE TABLE test_like_5x (p int CHECK (p > 0), 46 q int DEFAULT 99); 47CREATE TABLE test_like_5c (LIKE test_like_4 INCLUDING ALL) 48 INHERITS (test_like_5, test_like_5x); 49\d test_like_5c 50DROP TABLE test_like_4; 51DROP TABLE test_like_5, test_like_5x, test_like_5c; 52 53CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */ 54INSERT INTO inhg VALUES (5, 10); 55INSERT INTO inhg VALUES (20, 10); -- should fail 56DROP TABLE inhg; 57/* Multiple primary keys creation should fail */ 58CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, PRIMARY KEY(x)); /* fails */ 59CREATE TABLE inhz (xx text DEFAULT 'text', yy int UNIQUE); 60CREATE UNIQUE INDEX inhz_xx_idx on inhz (xx) WHERE xx <> 'test'; 61/* Ok to create multiple unique indexes */ 62CREATE TABLE inhg (x text UNIQUE, LIKE inhz INCLUDING INDEXES); 63INSERT INTO inhg (xx, yy, x) VALUES ('test', 5, 10); 64INSERT INTO inhg (xx, yy, x) VALUES ('test', 10, 15); 65INSERT INTO inhg (xx, yy, x) VALUES ('foo', 10, 15); -- should fail 66DROP TABLE inhg; 67DROP TABLE inhz; 68 69/* Use primary key imported by LIKE for self-referential FK constraint */ 70CREATE TABLE inhz (x text REFERENCES inhz, LIKE inhx INCLUDING INDEXES); 71\d inhz 72DROP TABLE inhz; 73 74-- including storage and comments 75CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) PRIMARY KEY, b text); 76CREATE INDEX ctlt1_b_key ON ctlt1 (b); 77CREATE INDEX ctlt1_fnidx ON ctlt1 ((a || b)); 78COMMENT ON COLUMN ctlt1.a IS 'A'; 79COMMENT ON COLUMN ctlt1.b IS 'B'; 80COMMENT ON CONSTRAINT ctlt1_a_check ON ctlt1 IS 't1_a_check'; 81COMMENT ON INDEX ctlt1_pkey IS 'index pkey'; 82COMMENT ON INDEX ctlt1_b_key IS 'index b_key'; 83ALTER TABLE ctlt1 ALTER COLUMN a SET STORAGE MAIN; 84 85CREATE TABLE ctlt2 (c text); 86ALTER TABLE ctlt2 ALTER COLUMN c SET STORAGE EXTERNAL; 87COMMENT ON COLUMN ctlt2.c IS 'C'; 88 89CREATE TABLE ctlt3 (a text CHECK (length(a) < 5), c text CHECK (length(c) < 7)); 90ALTER TABLE ctlt3 ALTER COLUMN c SET STORAGE EXTERNAL; 91ALTER TABLE ctlt3 ALTER COLUMN a SET STORAGE MAIN; 92CREATE INDEX ctlt3_fnidx ON ctlt3 ((a || c)); 93COMMENT ON COLUMN ctlt3.a IS 'A3'; 94COMMENT ON COLUMN ctlt3.c IS 'C'; 95COMMENT ON CONSTRAINT ctlt3_a_check ON ctlt3 IS 't3_a_check'; 96 97CREATE TABLE ctlt4 (a text, c text); 98ALTER TABLE ctlt4 ALTER COLUMN c SET STORAGE EXTERNAL; 99 100CREATE TABLE ctlt12_storage (LIKE ctlt1 INCLUDING STORAGE, LIKE ctlt2 INCLUDING STORAGE); 101\d+ ctlt12_storage 102CREATE TABLE ctlt12_comments (LIKE ctlt1 INCLUDING COMMENTS, LIKE ctlt2 INCLUDING COMMENTS); 103\d+ ctlt12_comments 104CREATE TABLE ctlt1_inh (LIKE ctlt1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INHERITS (ctlt1); 105\d+ ctlt1_inh 106SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 'ctlt1_inh'::regclass; 107CREATE TABLE ctlt13_inh () INHERITS (ctlt1, ctlt3); 108\d+ ctlt13_inh 109CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING COMMENTS INCLUDING STORAGE) INHERITS (ctlt1); 110\d+ ctlt13_like 111SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 'ctlt13_like'::regclass; 112 113CREATE TABLE ctlt_all (LIKE ctlt1 INCLUDING ALL); 114\d+ ctlt_all 115SELECT 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; 116 117CREATE TABLE inh_error1 () INHERITS (ctlt1, ctlt4); 118CREATE TABLE inh_error2 (LIKE ctlt4 INCLUDING STORAGE) INHERITS (ctlt1); 119 120-- Check that LIKE isn't confused by a system catalog of the same name 121CREATE TABLE pg_attrdef (LIKE ctlt1 INCLUDING ALL); 122\d+ public.pg_attrdef 123DROP TABLE public.pg_attrdef; 124 125-- Check that LIKE isn't confused when new table masks the old, either 126BEGIN; 127CREATE SCHEMA ctl_schema; 128SET LOCAL search_path = ctl_schema, public; 129CREATE TABLE ctlt1 (LIKE ctlt1 INCLUDING ALL); 130\d+ ctlt1 131ROLLBACK; 132 133DROP TABLE ctlt1, ctlt2, ctlt3, ctlt4, ctlt12_storage, ctlt12_comments, ctlt1_inh, ctlt13_inh, ctlt13_like, ctlt_all, ctla, ctlb CASCADE; 134 135 136/* LIKE with other relation kinds */ 137 138CREATE TABLE ctlt4 (a int, b text); 139 140CREATE SEQUENCE ctlseq1; 141CREATE TABLE ctlt10 (LIKE ctlseq1); -- fail 142 143CREATE VIEW ctlv1 AS SELECT * FROM ctlt4; 144CREATE TABLE ctlt11 (LIKE ctlv1); 145CREATE TABLE ctlt11a (LIKE ctlv1 INCLUDING ALL); 146 147CREATE TYPE ctlty1 AS (a int, b text); 148CREATE TABLE ctlt12 (LIKE ctlty1); 149 150DROP SEQUENCE ctlseq1; 151DROP TYPE ctlty1; 152DROP VIEW ctlv1; 153DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12; 154 155/* LIKE WITH OIDS */ 156CREATE TABLE has_oid (x INTEGER) WITH OIDS; 157CREATE TABLE no_oid (y INTEGER); 158CREATE TABLE like_test (z INTEGER, LIKE has_oid); 159SELECT oid FROM like_test; 160CREATE TABLE like_test2 (z INTEGER, LIKE no_oid); 161SELECT oid FROM like_test2; -- fail 162CREATE TABLE like_test3 (z INTEGER, LIKE has_oid, LIKE no_oid); 163SELECT oid FROM like_test3; 164CREATE TABLE like_test4 (z INTEGER, PRIMARY KEY(oid), LIKE has_oid); 165SELECT oid FROM like_test4; 166CREATE TABLE like_test5 (z INTEGER, LIKE no_oid) WITH OIDS; 167SELECT oid FROM like_test5; 168DROP TABLE has_oid, no_oid, like_test, like_test2, like_test3, 169 like_test4, like_test5; 170