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
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
|
/*
* 1.test CREATE INDEX
*/
-- Regular index with included columns
CREATE TABLE tbl (c1 int, c2 int, c3 int, c4 box);
INSERT INTO tbl SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
CREATE INDEX tbl_idx ON tbl using btree (c1, c2) INCLUDE (c3,c4);
-- must fail because of intersection of key and included columns
CREATE INDEX tbl_idx ON tbl using btree (c1, c2) INCLUDE (c1,c3);
ERROR: included columns must not intersect with key columns
SELECT pg_get_indexdef(i.indexrelid)
FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid
WHERE i.indrelid = 'tbl'::regclass ORDER BY c.relname;
pg_get_indexdef
--------------------------------------------------------------------------
CREATE INDEX tbl_idx ON public.tbl USING btree (c1, c2) INCLUDE (c3, c4)
(1 row)
DROP TABLE tbl;
-- Unique index and unique constraint
CREATE TABLE tbl (c1 int, c2 int, c3 int, c4 box);
INSERT INTO tbl SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
CREATE UNIQUE INDEX tbl_idx_unique ON tbl using btree (c1, c2) INCLUDE (c3, c4);
ALTER TABLE tbl add UNIQUE USING INDEX tbl_idx_unique;
ALTER TABLE tbl add UNIQUE (c1, c2) INCLUDE (c3, c4);
SELECT pg_get_indexdef(i.indexrelid)
FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid
WHERE i.indrelid = 'tbl'::regclass ORDER BY c.relname;
pg_get_indexdef
---------------------------------------------------------------------------------------------
CREATE UNIQUE INDEX tbl_c1_c2_c3_c4_key ON public.tbl USING btree (c1, c2) INCLUDE (c3, c4)
CREATE UNIQUE INDEX tbl_idx_unique ON public.tbl USING btree (c1, c2) INCLUDE (c3, c4)
(2 rows)
DROP TABLE tbl;
-- Unique index and unique constraint. Both must fail.
CREATE TABLE tbl (c1 int, c2 int, c3 int, c4 box);
INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
CREATE UNIQUE INDEX tbl_idx_unique ON tbl using btree (c1, c2) INCLUDE (c3, c4);
ERROR: could not create unique index "tbl_idx_unique"
DETAIL: Key (c1, c2)=(1, 2) is duplicated.
ALTER TABLE tbl add UNIQUE (c1, c2) INCLUDE (c3, c4);
ERROR: could not create unique index "tbl_c1_c2_c3_c4_key"
DETAIL: Key (c1, c2)=(1, 2) is duplicated.
DROP TABLE tbl;
-- PK constraint
CREATE TABLE tbl (c1 int, c2 int, c3 int, c4 box);
INSERT INTO tbl SELECT 1, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
ALTER TABLE tbl add PRIMARY KEY (c1, c2) INCLUDE (c3, c4);
SELECT pg_get_indexdef(i.indexrelid)
FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid
WHERE i.indrelid = 'tbl'::regclass ORDER BY c.relname;
pg_get_indexdef
----------------------------------------------------------------------------------
CREATE UNIQUE INDEX tbl_pkey ON public.tbl USING btree (c1, c2) INCLUDE (c3, c4)
(1 row)
DROP TABLE tbl;
CREATE TABLE tbl (c1 int, c2 int, c3 int, c4 box);
INSERT INTO tbl SELECT 1, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
CREATE UNIQUE INDEX tbl_idx_unique ON tbl using btree (c1, c2) INCLUDE (c3, c4);
ALTER TABLE tbl add PRIMARY KEY USING INDEX tbl_idx_unique;
SELECT pg_get_indexdef(i.indexrelid)
FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid
WHERE i.indrelid = 'tbl'::regclass ORDER BY c.relname;
pg_get_indexdef
----------------------------------------------------------------------------------------
CREATE UNIQUE INDEX tbl_idx_unique ON public.tbl USING btree (c1, c2) INCLUDE (c3, c4)
(1 row)
DROP TABLE tbl;
-- PK constraint. Must fail.
CREATE TABLE tbl (c1 int, c2 int, c3 int, c4 box);
INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
ALTER TABLE tbl add PRIMARY KEY (c1, c2) INCLUDE (c3, c4);
ERROR: could not create unique index "tbl_pkey"
DETAIL: Key (c1, c2)=(1, 2) is duplicated.
DROP TABLE tbl;
/*
* 2. Test CREATE TABLE with constraint
*/
CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box,
CONSTRAINT covering UNIQUE(c1,c2) INCLUDE(c3,c4));
SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid;
indexrelid | indnatts | indnkeyatts | indisunique | indisprimary | indkey | indclass
------------+----------+-------------+-------------+--------------+---------+-----------
covering | 4 | 2 | t | f | 1 2 3 4 | 1978 1978
(1 row)
SELECT pg_get_constraintdef(oid), conname, conkey, conincluding FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid;
pg_get_constraintdef | conname | conkey | conincluding
----------------------------------+----------+--------+--------------
UNIQUE (c1, c2) INCLUDE (c3, c4) | covering | {1,2} | {3,4}
(1 row)
-- ensure that constraint works
INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
ERROR: duplicate key value violates unique constraint "covering"
DETAIL: Key (c1, c2)=(1, 2) already exists.
DROP TABLE tbl;
CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box,
CONSTRAINT covering PRIMARY KEY(c1,c2) INCLUDE(c3,c4));
SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid;
indexrelid | indnatts | indnkeyatts | indisunique | indisprimary | indkey | indclass
------------+----------+-------------+-------------+--------------+---------+-----------
covering | 4 | 2 | t | t | 1 2 3 4 | 1978 1978
(1 row)
SELECT pg_get_constraintdef(oid), conname, conkey, conincluding FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid;
pg_get_constraintdef | conname | conkey | conincluding
---------------------------------------+----------+--------+--------------
PRIMARY KEY (c1, c2) INCLUDE (c3, c4) | covering | {1,2} | {3,4}
(1 row)
-- ensure that constraint works
INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
ERROR: duplicate key value violates unique constraint "covering"
DETAIL: Key (c1, c2)=(1, 2) already exists.
INSERT INTO tbl SELECT 1, NULL, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
ERROR: null value in column "c2" violates not-null constraint
DETAIL: Failing row contains (1, null, 3, (4,4),(4,4)).
INSERT INTO tbl SELECT x, 2*x, NULL, NULL FROM generate_series(1,10) AS x;
DROP TABLE tbl;
CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box,
UNIQUE(c1,c2) INCLUDE(c3,c4));
SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid;
indexrelid | indnatts | indnkeyatts | indisunique | indisprimary | indkey | indclass
---------------------+----------+-------------+-------------+--------------+---------+-----------
tbl_c1_c2_c3_c4_key | 4 | 2 | t | f | 1 2 3 4 | 1978 1978
(1 row)
SELECT pg_get_constraintdef(oid), conname, conkey, conincluding FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid;
pg_get_constraintdef | conname | conkey | conincluding
----------------------------------+---------------------+--------+--------------
UNIQUE (c1, c2) INCLUDE (c3, c4) | tbl_c1_c2_c3_c4_key | {1,2} | {3,4}
(1 row)
-- ensure that constraint works
INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
ERROR: duplicate key value violates unique constraint "tbl_c1_c2_c3_c4_key"
DETAIL: Key (c1, c2)=(1, 2) already exists.
DROP TABLE tbl;
CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box,
PRIMARY KEY(c1,c2) INCLUDE(c3,c4));
SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid;
indexrelid | indnatts | indnkeyatts | indisunique | indisprimary | indkey | indclass
------------+----------+-------------+-------------+--------------+---------+-----------
tbl_pkey | 4 | 2 | t | t | 1 2 3 4 | 1978 1978
(1 row)
SELECT pg_get_constraintdef(oid), conname, conkey, conincluding FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid;
pg_get_constraintdef | conname | conkey | conincluding
---------------------------------------+----------+--------+--------------
PRIMARY KEY (c1, c2) INCLUDE (c3, c4) | tbl_pkey | {1,2} | {3,4}
(1 row)
-- ensure that constraint works
INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
ERROR: duplicate key value violates unique constraint "tbl_pkey"
DETAIL: Key (c1, c2)=(1, 2) already exists.
INSERT INTO tbl SELECT 1, NULL, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
ERROR: null value in column "c2" violates not-null constraint
DETAIL: Failing row contains (1, null, 3, (4,4),(4,4)).
INSERT INTO tbl SELECT x, 2*x, NULL, NULL FROM generate_series(1,10) AS x;
DROP TABLE tbl;
CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box,
EXCLUDE USING btree (c1 WITH =) INCLUDE(c3,c4));
SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid;
indexrelid | indnatts | indnkeyatts | indisunique | indisprimary | indkey | indclass
-------------------+----------+-------------+-------------+--------------+--------+----------
tbl_c1_c3_c4_excl | 3 | 1 | f | f | 1 3 4 | 1978
(1 row)
SELECT pg_get_constraintdef(oid), conname, conkey, conincluding FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid;
pg_get_constraintdef | conname | conkey | conincluding
--------------------------------------------------+-------------------+--------+--------------
EXCLUDE USING btree (c1 WITH =) INCLUDE (c3, c4) | tbl_c1_c3_c4_excl | {1} | {3,4}
(1 row)
-- ensure that constraint works
INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
ERROR: conflicting key value violates exclusion constraint "tbl_c1_c3_c4_excl"
DETAIL: Key (c1)=(1) conflicts with existing key (c1)=(1).
INSERT INTO tbl SELECT x, 2*x, NULL, NULL FROM generate_series(1,10) AS x;
DROP TABLE tbl;
/*
* 3.0 Test ALTER TABLE DROP COLUMN.
* Any column deletion leads to index deletion.
*/
CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 int);
CREATE UNIQUE INDEX tbl_idx ON tbl using btree(c1, c2, c3, c4);
SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
indexdef
------------------------------------------------------------------------
CREATE UNIQUE INDEX tbl_idx ON public.tbl USING btree (c1, c2, c3, c4)
(1 row)
ALTER TABLE tbl DROP COLUMN c3;
SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
indexdef
----------
(0 rows)
DROP TABLE tbl;
/*
* 3.1 Test ALTER TABLE DROP COLUMN.
* Included column deletion leads to the index deletion,
* AS well AS key columns deletion. It's explained in documentation.
*/
CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box);
CREATE UNIQUE INDEX tbl_idx ON tbl using btree(c1, c2) INCLUDE(c3,c4);
SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
indexdef
---------------------------------------------------------------------------------
CREATE UNIQUE INDEX tbl_idx ON public.tbl USING btree (c1, c2) INCLUDE (c3, c4)
(1 row)
ALTER TABLE tbl DROP COLUMN c3;
SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
indexdef
----------
(0 rows)
DROP TABLE tbl;
/*
* 3.2 Test ALTER TABLE DROP COLUMN.
* Included column deletion leads to the index deletion.
* AS well AS key columns deletion. It's explained in documentation.
*/
CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, UNIQUE(c1, c2) INCLUDE(c3,c4));
SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
indexdef
---------------------------------------------------------------------------------------------
CREATE UNIQUE INDEX tbl_c1_c2_c3_c4_key ON public.tbl USING btree (c1, c2) INCLUDE (c3, c4)
(1 row)
ALTER TABLE tbl DROP COLUMN c3;
SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
indexdef
----------
(0 rows)
ALTER TABLE tbl DROP COLUMN c1;
SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
indexdef
----------
(0 rows)
DROP TABLE tbl;
/*
* 4. CREATE INDEX CONCURRENTLY
*/
CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, UNIQUE(c1, c2) INCLUDE(c3,c4));
INSERT INTO tbl SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,1000) AS x;
CREATE UNIQUE INDEX CONCURRENTLY on tbl (c1, c2) INCLUDE (c3, c4);
SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
indexdef
---------------------------------------------------------------------------------------------
CREATE UNIQUE INDEX tbl_c1_c2_c3_c4_idx ON public.tbl USING btree (c1, c2) INCLUDE (c3, c4)
CREATE UNIQUE INDEX tbl_c1_c2_c3_c4_key ON public.tbl USING btree (c1, c2) INCLUDE (c3, c4)
(2 rows)
DROP TABLE tbl;
/*
* 5. REINDEX
*/
CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, UNIQUE(c1, c2) INCLUDE(c3,c4));
SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
indexdef
---------------------------------------------------------------------------------------------
CREATE UNIQUE INDEX tbl_c1_c2_c3_c4_key ON public.tbl USING btree (c1, c2) INCLUDE (c3, c4)
(1 row)
ALTER TABLE tbl DROP COLUMN c3;
SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
indexdef
----------
(0 rows)
REINDEX INDEX tbl_c1_c2_c3_c4_key;
ERROR: relation "tbl_c1_c2_c3_c4_key" does not exist
SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
indexdef
----------
(0 rows)
ALTER TABLE tbl DROP COLUMN c1;
SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname;
indexdef
----------
(0 rows)
DROP TABLE tbl;
/*
* 7. Check various AMs. All but btree must fail.
*/
CREATE TABLE tbl (c1 int,c2 int, c3 box, c4 box);
CREATE INDEX on tbl USING brin(c1, c2) INCLUDE (c3, c4);
ERROR: access method "brin" does not support included columns
CREATE INDEX on tbl USING gist(c3) INCLUDE (c4);
ERROR: access method "gist" does not support included columns
CREATE INDEX on tbl USING spgist(c3) INCLUDE (c4);
ERROR: access method "spgist" does not support included columns
CREATE INDEX on tbl USING gin(c1, c2) INCLUDE (c3, c4);
ERROR: access method "gin" does not support included columns
CREATE INDEX on tbl USING hash(c1, c2) INCLUDE (c3, c4);
ERROR: access method "hash" does not support included columns
CREATE INDEX on tbl USING rtree(c1, c2) INCLUDE (c3, c4);
NOTICE: substituting access method "gist" for obsolete method "rtree"
ERROR: access method "gist" does not support included columns
CREATE INDEX on tbl USING btree(c1, c2) INCLUDE (c3, c4);
DROP TABLE tbl;
/*
* 8. Update, delete values in indexed table.
*/
CREATE TABLE tbl (c1 int, c2 int, c3 int, c4 box);
INSERT INTO tbl SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
CREATE UNIQUE INDEX tbl_idx_unique ON tbl using btree(c1, c2) INCLUDE (c3,c4);
UPDATE tbl SET c1 = 100 WHERE c1 = 2;
UPDATE tbl SET c1 = 1 WHERE c1 = 3;
-- should fail
UPDATE tbl SET c2 = 2 WHERE c1 = 1;
ERROR: duplicate key value violates unique constraint "tbl_idx_unique"
DETAIL: Key (c1, c2)=(1, 2) already exists.
UPDATE tbl SET c3 = 1;
DELETE FROM tbl WHERE c1 = 5 OR c3 = 12;
DROP TABLE tbl;
/*
* 9. Alter column type.
*/
CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, UNIQUE(c1, c2) INCLUDE(c3,c4));
INSERT INTO tbl SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
ALTER TABLE tbl ALTER c1 TYPE bigint;
ALTER TABLE tbl ALTER c3 TYPE bigint;
\d tbl
Table "public.tbl"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
c1 | bigint | | |
c2 | integer | | |
c3 | bigint | | |
c4 | box | | |
Indexes:
"tbl_c1_c2_c3_c4_key" UNIQUE CONSTRAINT, btree (c1, c2) INCLUDE (c3, c4)
DROP TABLE tbl;
|
