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