1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
|
/* Test inheritance of structure (LIKE) */
CREATE TABLE inhx (xx text DEFAULT 'text');
/*
* Test double inheritance
*
* Ensure that defaults are NOT included unless
* INCLUDING DEFAULTS is specified
*/
CREATE TABLE ctla (aa TEXT);
CREATE TABLE ctlb (bb TEXT) INHERITS (ctla);
CREATE TABLE foo (LIKE nonexistent);
ERROR: relation "nonexistent" does not exist
LINE 1: CREATE TABLE foo (LIKE nonexistent);
^
CREATE TABLE inhe (ee text, LIKE inhx) inherits (ctlb);
INSERT INTO inhe VALUES ('ee-col1', 'ee-col2', DEFAULT, 'ee-col4');
SELECT * FROM inhe; /* Columns aa, bb, xx value NULL, ee */
aa | bb | ee | xx
---------+---------+----+---------
ee-col1 | ee-col2 | | ee-col4
(1 row)
SELECT * FROM inhx; /* Empty set since LIKE inherits structure only */
xx
----
(0 rows)
SELECT * FROM ctlb; /* Has ee entry */
aa | bb
---------+---------
ee-col1 | ee-col2
(1 row)
SELECT * FROM ctla; /* Has ee entry */
aa
---------
ee-col1
(1 row)
CREATE TABLE inhf (LIKE inhx, LIKE inhx); /* Throw error */
ERROR: column "xx" specified more than once
CREATE TABLE inhf (LIKE inhx INCLUDING DEFAULTS INCLUDING CONSTRAINTS);
INSERT INTO inhf DEFAULT VALUES;
SELECT * FROM inhf; /* Single entry with value 'text' */
xx
------
text
(1 row)
ALTER TABLE inhx add constraint foo CHECK (xx = 'text');
ALTER TABLE inhx ADD PRIMARY KEY (xx);
CREATE TABLE inhg (LIKE inhx); /* Doesn't copy constraint */
INSERT INTO inhg VALUES ('foo');
DROP TABLE inhg;
CREATE TABLE inhg (x text, LIKE inhx INCLUDING CONSTRAINTS, y text); /* Copies constraints */
INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds */
INSERT INTO inhg VALUES ('x', 'text', 'y'); /* Succeeds -- Unique constraints not copied */
INSERT INTO inhg VALUES ('x', 'foo', 'y'); /* fails due to constraint */
ERROR: new row for relation "inhg" violates check constraint "foo"
DETAIL: Failing row contains (x, foo, y).
SELECT * FROM inhg; /* Two records with three columns in order x=x, xx=text, y=y */
x | xx | y
---+------+---
x | text | y
x | text | y
(2 rows)
DROP TABLE inhg;
CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */
INSERT INTO inhg VALUES (5, 10);
INSERT INTO inhg VALUES (20, 10); -- should fail
ERROR: duplicate key value violates unique constraint "inhg_pkey"
DETAIL: Key (xx)=(10) already exists.
DROP TABLE inhg;
/* Multiple primary keys creation should fail */
CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, PRIMARY KEY(x)); /* fails */
ERROR: multiple primary keys for table "inhg" are not allowed
CREATE TABLE inhz (xx text DEFAULT 'text', yy int UNIQUE);
CREATE UNIQUE INDEX inhz_xx_idx on inhz (xx) WHERE xx <> 'test';
/* Ok to create multiple unique indexes */
CREATE TABLE inhg (x text UNIQUE, LIKE inhz INCLUDING INDEXES);
INSERT INTO inhg (xx, yy, x) VALUES ('test', 5, 10);
INSERT INTO inhg (xx, yy, x) VALUES ('test', 10, 15);
INSERT INTO inhg (xx, yy, x) VALUES ('foo', 10, 15); -- should fail
ERROR: duplicate key value violates unique constraint "inhg_x_key"
DETAIL: Key (x)=(15) already exists.
DROP TABLE inhg;
DROP TABLE inhz;
-- including storage and comments
CREATE TABLE ctlt1 (a text CHECK (length(a) > 2) PRIMARY KEY, b text);
CREATE INDEX ctlt1_b_key ON ctlt1 (b);
CREATE INDEX ctlt1_fnidx ON ctlt1 ((a || b));
COMMENT ON COLUMN ctlt1.a IS 'A';
COMMENT ON COLUMN ctlt1.b IS 'B';
COMMENT ON CONSTRAINT ctlt1_a_check ON ctlt1 IS 't1_a_check';
COMMENT ON INDEX ctlt1_pkey IS 'index pkey';
COMMENT ON INDEX ctlt1_b_key IS 'index b_key';
ALTER TABLE ctlt1 ALTER COLUMN a SET STORAGE MAIN;
CREATE TABLE ctlt2 (c text);
ALTER TABLE ctlt2 ALTER COLUMN c SET STORAGE EXTERNAL;
COMMENT ON COLUMN ctlt2.c IS 'C';
CREATE TABLE ctlt3 (a text CHECK (length(a) < 5), c text);
ALTER TABLE ctlt3 ALTER COLUMN c SET STORAGE EXTERNAL;
ALTER TABLE ctlt3 ALTER COLUMN a SET STORAGE MAIN;
COMMENT ON COLUMN ctlt3.a IS 'A3';
COMMENT ON COLUMN ctlt3.c IS 'C';
COMMENT ON CONSTRAINT ctlt3_a_check ON ctlt3 IS 't3_a_check';
CREATE TABLE ctlt4 (a text, c text);
ALTER TABLE ctlt4 ALTER COLUMN c SET STORAGE EXTERNAL;
CREATE TABLE ctlt12_storage (LIKE ctlt1 INCLUDING STORAGE, LIKE ctlt2 INCLUDING STORAGE);
\d+ ctlt12_storage
Table "public.ctlt12_storage"
Column | Type | Modifiers | Storage | Stats target | Description
--------+------+-----------+----------+--------------+-------------
a | text | not null | main | |
b | text | | extended | |
c | text | | external | |
CREATE TABLE ctlt12_comments (LIKE ctlt1 INCLUDING COMMENTS, LIKE ctlt2 INCLUDING COMMENTS);
\d+ ctlt12_comments
Table "public.ctlt12_comments"
Column | Type | Modifiers | Storage | Stats target | Description
--------+------+-----------+----------+--------------+-------------
a | text | not null | extended | | A
b | text | | extended | | B
c | text | | extended | | C
CREATE TABLE ctlt1_inh (LIKE ctlt1 INCLUDING CONSTRAINTS INCLUDING COMMENTS) INHERITS (ctlt1);
NOTICE: merging column "a" with inherited definition
NOTICE: merging column "b" with inherited definition
NOTICE: merging constraint "ctlt1_a_check" with inherited definition
\d+ ctlt1_inh
Table "public.ctlt1_inh"
Column | Type | Modifiers | Storage | Stats target | Description
--------+------+-----------+----------+--------------+-------------
a | text | not null | main | | A
b | text | | extended | | B
Check constraints:
"ctlt1_a_check" CHECK (length(a) > 2)
Inherits: ctlt1
SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 'ctlt1_inh'::regclass;
description
-------------
t1_a_check
(1 row)
CREATE TABLE ctlt13_inh () INHERITS (ctlt1, ctlt3);
NOTICE: merging multiple inherited definitions of column "a"
\d+ ctlt13_inh
Table "public.ctlt13_inh"
Column | Type | Modifiers | Storage | Stats target | Description
--------+------+-----------+----------+--------------+-------------
a | text | not null | main | |
b | text | | extended | |
c | text | | external | |
Check constraints:
"ctlt1_a_check" CHECK (length(a) > 2)
"ctlt3_a_check" CHECK (length(a) < 5)
Inherits: ctlt1,
ctlt3
CREATE TABLE ctlt13_like (LIKE ctlt3 INCLUDING CONSTRAINTS INCLUDING COMMENTS INCLUDING STORAGE) INHERITS (ctlt1);
NOTICE: merging column "a" with inherited definition
\d+ ctlt13_like
Table "public.ctlt13_like"
Column | Type | Modifiers | Storage | Stats target | Description
--------+------+-----------+----------+--------------+-------------
a | text | not null | main | | A3
b | text | | extended | |
c | text | | external | | C
Check constraints:
"ctlt1_a_check" CHECK (length(a) > 2)
"ctlt3_a_check" CHECK (length(a) < 5)
Inherits: ctlt1
SELECT description FROM pg_description, pg_constraint c WHERE classoid = 'pg_constraint'::regclass AND objoid = c.oid AND c.conrelid = 'ctlt13_like'::regclass;
description
-------------
t3_a_check
(1 row)
CREATE TABLE ctlt_all (LIKE ctlt1 INCLUDING ALL);
\d+ ctlt_all
Table "public.ctlt_all"
Column | Type | Modifiers | Storage | Stats target | Description
--------+------+-----------+----------+--------------+-------------
a | text | not null | main | | A
b | text | | extended | | B
Indexes:
"ctlt_all_pkey" PRIMARY KEY, btree (a)
"ctlt_all_b_idx" btree (b)
"ctlt_all_expr_idx" btree ((a || b))
Check constraints:
"ctlt1_a_check" CHECK (length(a) > 2)
SELECT 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;
relname | objsubid | description
----------------+----------+-------------
ctlt_all_b_idx | 0 | index b_key
ctlt_all_pkey | 0 | index pkey
(2 rows)
CREATE TABLE inh_error1 () INHERITS (ctlt1, ctlt4);
NOTICE: merging multiple inherited definitions of column "a"
ERROR: inherited column "a" has a storage parameter conflict
DETAIL: MAIN versus EXTENDED
CREATE TABLE inh_error2 (LIKE ctlt4 INCLUDING STORAGE) INHERITS (ctlt1);
NOTICE: merging column "a" with inherited definition
ERROR: column "a" has a storage parameter conflict
DETAIL: MAIN versus EXTENDED
DROP TABLE ctlt1, ctlt2, ctlt3, ctlt4, ctlt12_storage, ctlt12_comments, ctlt1_inh, ctlt13_inh, ctlt13_like, ctlt_all, ctla, ctlb CASCADE;
NOTICE: drop cascades to table inhe
/* LIKE with other relation kinds */
CREATE TABLE ctlt4 (a int, b text);
CREATE SEQUENCE ctlseq1;
CREATE TABLE ctlt10 (LIKE ctlseq1); -- fail
ERROR: "ctlseq1" is not a table, view, materialized view, composite type, or foreign table
LINE 1: CREATE TABLE ctlt10 (LIKE ctlseq1);
^
CREATE VIEW ctlv1 AS SELECT * FROM ctlt4;
CREATE TABLE ctlt11 (LIKE ctlv1);
CREATE TABLE ctlt11a (LIKE ctlv1 INCLUDING ALL);
CREATE TYPE ctlty1 AS (a int, b text);
CREATE TABLE ctlt12 (LIKE ctlty1);
DROP SEQUENCE ctlseq1;
DROP TYPE ctlty1;
DROP VIEW ctlv1;
DROP TABLE IF EXISTS ctlt4, ctlt10, ctlt11, ctlt11a, ctlt12;
NOTICE: table "ctlt10" does not exist, skipping
|
