summaryrefslogtreecommitdiffstats
path: root/src/test/regress/expected/index_including.out
blob: ea8b2454bf8cd45951d53a05bfef3612099e398d (plain)
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
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
/*
 * 1.test CREATE INDEX
 *
 * Deliberately avoid dropping objects in this section, to get some pg_dump
 * coverage.
 */
-- Regular index with included columns
CREATE TABLE tbl_include_reg (c1 int, c2 int, c3 int, c4 box);
INSERT INTO tbl_include_reg SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
CREATE INDEX tbl_include_reg_idx ON tbl_include_reg (c1, c2) INCLUDE (c3, c4);
-- duplicate column is pretty pointless, but we allow it anyway
CREATE INDEX ON tbl_include_reg (c1, c2) INCLUDE (c1, c3);
SELECT pg_get_indexdef(i.indexrelid)
FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid
WHERE i.indrelid = 'tbl_include_reg'::regclass ORDER BY c.relname;
                                                pg_get_indexdef                                                
---------------------------------------------------------------------------------------------------------------
 CREATE INDEX tbl_include_reg_c1_c2_c11_c3_idx ON public.tbl_include_reg USING btree (c1, c2) INCLUDE (c1, c3)
 CREATE INDEX tbl_include_reg_idx ON public.tbl_include_reg USING btree (c1, c2) INCLUDE (c3, c4)
(2 rows)

\d tbl_include_reg_idx
  Index "public.tbl_include_reg_idx"
 Column |  Type   | Key? | Definition 
--------+---------+------+------------
 c1     | integer | yes  | c1
 c2     | integer | yes  | c2
 c3     | integer | no   | c3
 c4     | box     | no   | c4
btree, for table "public.tbl_include_reg"

-- Unique index and unique constraint
CREATE TABLE tbl_include_unique1 (c1 int, c2 int, c3 int, c4 box);
INSERT INTO tbl_include_unique1 SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
CREATE UNIQUE INDEX tbl_include_unique1_idx_unique ON tbl_include_unique1 using btree (c1, c2) INCLUDE (c3, c4);
ALTER TABLE tbl_include_unique1 add UNIQUE USING INDEX tbl_include_unique1_idx_unique;
ALTER TABLE tbl_include_unique1 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_include_unique1'::regclass ORDER BY c.relname;
                                                       pg_get_indexdef                                                       
-----------------------------------------------------------------------------------------------------------------------------
 CREATE UNIQUE INDEX tbl_include_unique1_c1_c2_c3_c4_key ON public.tbl_include_unique1 USING btree (c1, c2) INCLUDE (c3, c4)
 CREATE UNIQUE INDEX tbl_include_unique1_idx_unique ON public.tbl_include_unique1 USING btree (c1, c2) INCLUDE (c3, c4)
(2 rows)

-- Unique index and unique constraint. Both must fail.
CREATE TABLE tbl_include_unique2 (c1 int, c2 int, c3 int, c4 box);
INSERT INTO tbl_include_unique2 SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
CREATE UNIQUE INDEX tbl_include_unique2_idx_unique ON tbl_include_unique2 using btree (c1, c2) INCLUDE (c3, c4);
ERROR:  could not create unique index "tbl_include_unique2_idx_unique"
DETAIL:  Key (c1, c2)=(1, 2) is duplicated.
ALTER TABLE tbl_include_unique2 add UNIQUE (c1, c2) INCLUDE (c3, c4);
ERROR:  could not create unique index "tbl_include_unique2_c1_c2_c3_c4_key"
DETAIL:  Key (c1, c2)=(1, 2) is duplicated.
-- PK constraint
CREATE TABLE tbl_include_pk (c1 int, c2 int, c3 int, c4 box);
INSERT INTO tbl_include_pk SELECT 1, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
ALTER TABLE tbl_include_pk 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_include_pk'::regclass ORDER BY c.relname;
                                            pg_get_indexdef                                             
--------------------------------------------------------------------------------------------------------
 CREATE UNIQUE INDEX tbl_include_pk_pkey ON public.tbl_include_pk USING btree (c1, c2) INCLUDE (c3, c4)
(1 row)

CREATE TABLE tbl_include_box (c1 int, c2 int, c3 int, c4 box);
INSERT INTO tbl_include_box SELECT 1, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
CREATE UNIQUE INDEX tbl_include_box_idx_unique ON tbl_include_box using btree (c1, c2) INCLUDE (c3, c4);
ALTER TABLE tbl_include_box add PRIMARY KEY USING INDEX tbl_include_box_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_include_box'::regclass ORDER BY c.relname;
                                                pg_get_indexdef                                                 
----------------------------------------------------------------------------------------------------------------
 CREATE UNIQUE INDEX tbl_include_box_idx_unique ON public.tbl_include_box USING btree (c1, c2) INCLUDE (c3, c4)
(1 row)

-- PK constraint. Must fail.
CREATE TABLE tbl_include_box_pk (c1 int, c2 int, c3 int, c4 box);
INSERT INTO tbl_include_box_pk SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x;
ALTER TABLE tbl_include_box_pk add PRIMARY KEY (c1, c2) INCLUDE (c3, c4);
ERROR:  could not create unique index "tbl_include_box_pk_pkey"
DETAIL:  Key (c1, c2)=(1, 2) is duplicated.
/*
 * 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 FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid;
       pg_get_constraintdef       | conname  | conkey 
----------------------------------+----------+--------
 UNIQUE (c1, c2) INCLUDE (c3, c4) | covering | {1,2}
(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 FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid;
         pg_get_constraintdef          | conname  | conkey 
---------------------------------------+----------+--------
 PRIMARY KEY (c1, c2) INCLUDE (c3, c4) | covering | {1,2}
(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" of relation "tbl" 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,300) AS x;
explain (costs off)
select * from tbl where (c1,c2,c3) < (2,5,1);
                   QUERY PLAN                   
------------------------------------------------
 Bitmap Heap Scan on tbl
   Filter: (ROW(c1, c2, c3) < ROW(2, 5, 1))
   ->  Bitmap Index Scan on covering
         Index Cond: (ROW(c1, c2) <= ROW(2, 5))
(4 rows)

select * from tbl where (c1,c2,c3) < (2,5,1);
 c1 | c2 | c3 | c4 
----+----+----+----
  1 |  2 |    | 
  2 |  4 |    | 
(2 rows)

-- row comparison that compares high key at page boundary
SET enable_seqscan = off;
explain (costs off)
select * from tbl where (c1,c2,c3) < (262,1,1) limit 1;
                     QUERY PLAN                     
----------------------------------------------------
 Limit
   ->  Index Only Scan using covering on tbl
         Index Cond: (ROW(c1, c2) <= ROW(262, 1))
         Filter: (ROW(c1, c2, c3) < ROW(262, 1, 1))
(4 rows)

select * from tbl where (c1,c2,c3) < (262,1,1) limit 1;
 c1 | c2 | c3 | c4 
----+----+----+----
  1 |  2 |    | 
(1 row)

DROP TABLE tbl;
RESET enable_seqscan;
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 FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid;
       pg_get_constraintdef       |       conname       | conkey 
----------------------------------+---------------------+--------
 UNIQUE (c1, c2) INCLUDE (c3, c4) | tbl_c1_c2_c3_c4_key | {1,2}
(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 FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid;
         pg_get_constraintdef          | conname  | conkey 
---------------------------------------+----------+--------
 PRIMARY KEY (c1, c2) INCLUDE (c3, c4) | tbl_pkey | {1,2}
(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" of relation "tbl" 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 FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid;
               pg_get_constraintdef               |      conname      | conkey 
--------------------------------------------------+-------------------+--------
 EXCLUDE USING btree (c1 WITH =) INCLUDE (c3, c4) | tbl_c1_c3_c4_excl | {1}
(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;
/*
 * 3.3 Test ALTER TABLE SET STATISTICS
 */
CREATE TABLE tbl (c1 int, c2 int);
CREATE INDEX tbl_idx ON tbl (c1, (c1+0)) INCLUDE (c2);
ALTER INDEX tbl_idx ALTER COLUMN 1 SET STATISTICS 1000;
ERROR:  cannot alter statistics on non-expression column "c1" of index "tbl_idx"
HINT:  Alter statistics on table column instead.
ALTER INDEX tbl_idx ALTER COLUMN 2 SET STATISTICS 1000;
ALTER INDEX tbl_idx ALTER COLUMN 3 SET STATISTICS 1000;
ERROR:  cannot alter statistics on included column "c2" of index "tbl_idx"
ALTER INDEX tbl_idx ALTER COLUMN 4 SET STATISTICS 1000;
ERROR:  column number 4 of relation "tbl_idx" does not exist
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, gist and spgist 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 (c1, c4);
CREATE INDEX on tbl USING spgist(c3) INCLUDE (c4);
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(c3) INCLUDE (c1, c4);
NOTICE:  substituting access method "gist" for obsolete method "rtree"
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;
/*
 * 10. Test coverage for names stored as cstrings in indexes
 */
CREATE TABLE nametbl (c1 int, c2 name, c3 float);
CREATE INDEX nametbl_c1_c2_idx ON nametbl (c2, c1) INCLUDE (c3);
INSERT INTO nametbl VALUES(1, 'two', 3.0);
VACUUM nametbl;
SET enable_seqscan = 0;
-- Ensure we get an index only scan plan
EXPLAIN (COSTS OFF) SELECT c2, c1, c3 FROM nametbl WHERE c2 = 'two' AND c1 = 1;
                     QUERY PLAN                     
----------------------------------------------------
 Index Only Scan using nametbl_c1_c2_idx on nametbl
   Index Cond: ((c2 = 'two'::name) AND (c1 = 1))
(2 rows)

-- Validate the results look sane
SELECT c2, c1, c3 FROM nametbl WHERE c2 = 'two' AND c1 = 1;
 c2  | c1 | c3 
-----+----+----
 two |  1 |  3
(1 row)

RESET enable_seqscan;
DROP TABLE nametbl;