1/* 2 * 1.test CREATE INDEX 3 * 4 * Deliberately avoid dropping objects in this section, to get some pg_dump 5 * coverage. 6 */ 7-- Regular index with included columns 8CREATE TABLE tbl_include_reg (c1 int, c2 int, c3 int, c4 box); 9INSERT INTO tbl_include_reg SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 10CREATE INDEX tbl_include_reg_idx ON tbl_include_reg (c1, c2) INCLUDE (c3, c4); 11-- duplicate column is pretty pointless, but we allow it anyway 12CREATE INDEX ON tbl_include_reg (c1, c2) INCLUDE (c1, c3); 13SELECT pg_get_indexdef(i.indexrelid) 14FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid 15WHERE i.indrelid = 'tbl_include_reg'::regclass ORDER BY c.relname; 16 pg_get_indexdef 17--------------------------------------------------------------------------------------------------------------- 18 CREATE INDEX tbl_include_reg_c1_c2_c11_c3_idx ON public.tbl_include_reg USING btree (c1, c2) INCLUDE (c1, c3) 19 CREATE INDEX tbl_include_reg_idx ON public.tbl_include_reg USING btree (c1, c2) INCLUDE (c3, c4) 20(2 rows) 21 22\d tbl_include_reg_idx 23 Index "public.tbl_include_reg_idx" 24 Column | Type | Key? | Definition 25--------+---------+------+------------ 26 c1 | integer | yes | c1 27 c2 | integer | yes | c2 28 c3 | integer | no | c3 29 c4 | box | no | c4 30btree, for table "public.tbl_include_reg" 31 32-- Unique index and unique constraint 33CREATE TABLE tbl_include_unique1 (c1 int, c2 int, c3 int, c4 box); 34INSERT INTO tbl_include_unique1 SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 35CREATE UNIQUE INDEX tbl_include_unique1_idx_unique ON tbl_include_unique1 using btree (c1, c2) INCLUDE (c3, c4); 36ALTER TABLE tbl_include_unique1 add UNIQUE USING INDEX tbl_include_unique1_idx_unique; 37ALTER TABLE tbl_include_unique1 add UNIQUE (c1, c2) INCLUDE (c3, c4); 38SELECT pg_get_indexdef(i.indexrelid) 39FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid 40WHERE i.indrelid = 'tbl_include_unique1'::regclass ORDER BY c.relname; 41 pg_get_indexdef 42----------------------------------------------------------------------------------------------------------------------------- 43 CREATE UNIQUE INDEX tbl_include_unique1_c1_c2_c3_c4_key ON public.tbl_include_unique1 USING btree (c1, c2) INCLUDE (c3, c4) 44 CREATE UNIQUE INDEX tbl_include_unique1_idx_unique ON public.tbl_include_unique1 USING btree (c1, c2) INCLUDE (c3, c4) 45(2 rows) 46 47-- Unique index and unique constraint. Both must fail. 48CREATE TABLE tbl_include_unique2 (c1 int, c2 int, c3 int, c4 box); 49INSERT INTO tbl_include_unique2 SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 50CREATE UNIQUE INDEX tbl_include_unique2_idx_unique ON tbl_include_unique2 using btree (c1, c2) INCLUDE (c3, c4); 51ERROR: could not create unique index "tbl_include_unique2_idx_unique" 52DETAIL: Key (c1, c2)=(1, 2) is duplicated. 53ALTER TABLE tbl_include_unique2 add UNIQUE (c1, c2) INCLUDE (c3, c4); 54ERROR: could not create unique index "tbl_include_unique2_c1_c2_c3_c4_key" 55DETAIL: Key (c1, c2)=(1, 2) is duplicated. 56-- PK constraint 57CREATE TABLE tbl_include_pk (c1 int, c2 int, c3 int, c4 box); 58INSERT INTO tbl_include_pk SELECT 1, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 59ALTER TABLE tbl_include_pk add PRIMARY KEY (c1, c2) INCLUDE (c3, c4); 60SELECT pg_get_indexdef(i.indexrelid) 61FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid 62WHERE i.indrelid = 'tbl_include_pk'::regclass ORDER BY c.relname; 63 pg_get_indexdef 64-------------------------------------------------------------------------------------------------------- 65 CREATE UNIQUE INDEX tbl_include_pk_pkey ON public.tbl_include_pk USING btree (c1, c2) INCLUDE (c3, c4) 66(1 row) 67 68CREATE TABLE tbl_include_box (c1 int, c2 int, c3 int, c4 box); 69INSERT INTO tbl_include_box SELECT 1, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 70CREATE UNIQUE INDEX tbl_include_box_idx_unique ON tbl_include_box using btree (c1, c2) INCLUDE (c3, c4); 71ALTER TABLE tbl_include_box add PRIMARY KEY USING INDEX tbl_include_box_idx_unique; 72SELECT pg_get_indexdef(i.indexrelid) 73FROM pg_index i JOIN pg_class c ON i.indexrelid = c.oid 74WHERE i.indrelid = 'tbl_include_box'::regclass ORDER BY c.relname; 75 pg_get_indexdef 76---------------------------------------------------------------------------------------------------------------- 77 CREATE UNIQUE INDEX tbl_include_box_idx_unique ON public.tbl_include_box USING btree (c1, c2) INCLUDE (c3, c4) 78(1 row) 79 80-- PK constraint. Must fail. 81CREATE TABLE tbl_include_box_pk (c1 int, c2 int, c3 int, c4 box); 82INSERT INTO tbl_include_box_pk SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 83ALTER TABLE tbl_include_box_pk add PRIMARY KEY (c1, c2) INCLUDE (c3, c4); 84ERROR: could not create unique index "tbl_include_box_pk_pkey" 85DETAIL: Key (c1, c2)=(1, 2) is duplicated. 86/* 87 * 2. Test CREATE TABLE with constraint 88 */ 89CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, 90 CONSTRAINT covering UNIQUE(c1,c2) INCLUDE(c3,c4)); 91SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid; 92 indexrelid | indnatts | indnkeyatts | indisunique | indisprimary | indkey | indclass 93------------+----------+-------------+-------------+--------------+---------+----------- 94 covering | 4 | 2 | t | f | 1 2 3 4 | 1978 1978 95(1 row) 96 97SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid; 98 pg_get_constraintdef | conname | conkey 99----------------------------------+----------+-------- 100 UNIQUE (c1, c2) INCLUDE (c3, c4) | covering | {1,2} 101(1 row) 102 103-- ensure that constraint works 104INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 105ERROR: duplicate key value violates unique constraint "covering" 106DETAIL: Key (c1, c2)=(1, 2) already exists. 107DROP TABLE tbl; 108CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, 109 CONSTRAINT covering PRIMARY KEY(c1,c2) INCLUDE(c3,c4)); 110SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid; 111 indexrelid | indnatts | indnkeyatts | indisunique | indisprimary | indkey | indclass 112------------+----------+-------------+-------------+--------------+---------+----------- 113 covering | 4 | 2 | t | t | 1 2 3 4 | 1978 1978 114(1 row) 115 116SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid; 117 pg_get_constraintdef | conname | conkey 118---------------------------------------+----------+-------- 119 PRIMARY KEY (c1, c2) INCLUDE (c3, c4) | covering | {1,2} 120(1 row) 121 122-- ensure that constraint works 123INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 124ERROR: duplicate key value violates unique constraint "covering" 125DETAIL: Key (c1, c2)=(1, 2) already exists. 126INSERT INTO tbl SELECT 1, NULL, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 127ERROR: null value in column "c2" of relation "tbl" violates not-null constraint 128DETAIL: Failing row contains (1, null, 3, (4,4),(4,4)). 129INSERT INTO tbl SELECT x, 2*x, NULL, NULL FROM generate_series(1,300) AS x; 130explain (costs off) 131select * from tbl where (c1,c2,c3) < (2,5,1); 132 QUERY PLAN 133------------------------------------------------ 134 Bitmap Heap Scan on tbl 135 Filter: (ROW(c1, c2, c3) < ROW(2, 5, 1)) 136 -> Bitmap Index Scan on covering 137 Index Cond: (ROW(c1, c2) <= ROW(2, 5)) 138(4 rows) 139 140select * from tbl where (c1,c2,c3) < (2,5,1); 141 c1 | c2 | c3 | c4 142----+----+----+---- 143 1 | 2 | | 144 2 | 4 | | 145(2 rows) 146 147-- row comparison that compares high key at page boundary 148SET enable_seqscan = off; 149explain (costs off) 150select * from tbl where (c1,c2,c3) < (262,1,1) limit 1; 151 QUERY PLAN 152---------------------------------------------------- 153 Limit 154 -> Index Only Scan using covering on tbl 155 Index Cond: (ROW(c1, c2) <= ROW(262, 1)) 156 Filter: (ROW(c1, c2, c3) < ROW(262, 1, 1)) 157(4 rows) 158 159select * from tbl where (c1,c2,c3) < (262,1,1) limit 1; 160 c1 | c2 | c3 | c4 161----+----+----+---- 162 1 | 2 | | 163(1 row) 164 165DROP TABLE tbl; 166RESET enable_seqscan; 167CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, 168 UNIQUE(c1,c2) INCLUDE(c3,c4)); 169SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid; 170 indexrelid | indnatts | indnkeyatts | indisunique | indisprimary | indkey | indclass 171---------------------+----------+-------------+-------------+--------------+---------+----------- 172 tbl_c1_c2_c3_c4_key | 4 | 2 | t | f | 1 2 3 4 | 1978 1978 173(1 row) 174 175SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid; 176 pg_get_constraintdef | conname | conkey 177----------------------------------+---------------------+-------- 178 UNIQUE (c1, c2) INCLUDE (c3, c4) | tbl_c1_c2_c3_c4_key | {1,2} 179(1 row) 180 181-- ensure that constraint works 182INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 183ERROR: duplicate key value violates unique constraint "tbl_c1_c2_c3_c4_key" 184DETAIL: Key (c1, c2)=(1, 2) already exists. 185DROP TABLE tbl; 186CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, 187 PRIMARY KEY(c1,c2) INCLUDE(c3,c4)); 188SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid; 189 indexrelid | indnatts | indnkeyatts | indisunique | indisprimary | indkey | indclass 190------------+----------+-------------+-------------+--------------+---------+----------- 191 tbl_pkey | 4 | 2 | t | t | 1 2 3 4 | 1978 1978 192(1 row) 193 194SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid; 195 pg_get_constraintdef | conname | conkey 196---------------------------------------+----------+-------- 197 PRIMARY KEY (c1, c2) INCLUDE (c3, c4) | tbl_pkey | {1,2} 198(1 row) 199 200-- ensure that constraint works 201INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 202ERROR: duplicate key value violates unique constraint "tbl_pkey" 203DETAIL: Key (c1, c2)=(1, 2) already exists. 204INSERT INTO tbl SELECT 1, NULL, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 205ERROR: null value in column "c2" of relation "tbl" violates not-null constraint 206DETAIL: Failing row contains (1, null, 3, (4,4),(4,4)). 207INSERT INTO tbl SELECT x, 2*x, NULL, NULL FROM generate_series(1,10) AS x; 208DROP TABLE tbl; 209CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, 210 EXCLUDE USING btree (c1 WITH =) INCLUDE(c3,c4)); 211SELECT indexrelid::regclass, indnatts, indnkeyatts, indisunique, indisprimary, indkey, indclass FROM pg_index WHERE indrelid = 'tbl'::regclass::oid; 212 indexrelid | indnatts | indnkeyatts | indisunique | indisprimary | indkey | indclass 213-------------------+----------+-------------+-------------+--------------+--------+---------- 214 tbl_c1_c3_c4_excl | 3 | 1 | f | f | 1 3 4 | 1978 215(1 row) 216 217SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'tbl'::regclass::oid; 218 pg_get_constraintdef | conname | conkey 219--------------------------------------------------+-------------------+-------- 220 EXCLUDE USING btree (c1 WITH =) INCLUDE (c3, c4) | tbl_c1_c3_c4_excl | {1} 221(1 row) 222 223-- ensure that constraint works 224INSERT INTO tbl SELECT 1, 2, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 225ERROR: conflicting key value violates exclusion constraint "tbl_c1_c3_c4_excl" 226DETAIL: Key (c1)=(1) conflicts with existing key (c1)=(1). 227INSERT INTO tbl SELECT x, 2*x, NULL, NULL FROM generate_series(1,10) AS x; 228DROP TABLE tbl; 229/* 230 * 3.0 Test ALTER TABLE DROP COLUMN. 231 * Any column deletion leads to index deletion. 232 */ 233CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 int); 234CREATE UNIQUE INDEX tbl_idx ON tbl using btree(c1, c2, c3, c4); 235SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 236 indexdef 237------------------------------------------------------------------------ 238 CREATE UNIQUE INDEX tbl_idx ON public.tbl USING btree (c1, c2, c3, c4) 239(1 row) 240 241ALTER TABLE tbl DROP COLUMN c3; 242SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 243 indexdef 244---------- 245(0 rows) 246 247DROP TABLE tbl; 248/* 249 * 3.1 Test ALTER TABLE DROP COLUMN. 250 * Included column deletion leads to the index deletion, 251 * AS well AS key columns deletion. It's explained in documentation. 252 */ 253CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box); 254CREATE UNIQUE INDEX tbl_idx ON tbl using btree(c1, c2) INCLUDE(c3,c4); 255SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 256 indexdef 257--------------------------------------------------------------------------------- 258 CREATE UNIQUE INDEX tbl_idx ON public.tbl USING btree (c1, c2) INCLUDE (c3, c4) 259(1 row) 260 261ALTER TABLE tbl DROP COLUMN c3; 262SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 263 indexdef 264---------- 265(0 rows) 266 267DROP TABLE tbl; 268/* 269 * 3.2 Test ALTER TABLE DROP COLUMN. 270 * Included column deletion leads to the index deletion. 271 * AS well AS key columns deletion. It's explained in documentation. 272 */ 273CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, UNIQUE(c1, c2) INCLUDE(c3,c4)); 274SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 275 indexdef 276--------------------------------------------------------------------------------------------- 277 CREATE UNIQUE INDEX tbl_c1_c2_c3_c4_key ON public.tbl USING btree (c1, c2) INCLUDE (c3, c4) 278(1 row) 279 280ALTER TABLE tbl DROP COLUMN c3; 281SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 282 indexdef 283---------- 284(0 rows) 285 286ALTER TABLE tbl DROP COLUMN c1; 287SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 288 indexdef 289---------- 290(0 rows) 291 292DROP TABLE tbl; 293/* 294 * 3.3 Test ALTER TABLE SET STATISTICS 295 */ 296CREATE TABLE tbl (c1 int, c2 int); 297CREATE INDEX tbl_idx ON tbl (c1, (c1+0)) INCLUDE (c2); 298ALTER INDEX tbl_idx ALTER COLUMN 1 SET STATISTICS 1000; 299ERROR: cannot alter statistics on non-expression column "c1" of index "tbl_idx" 300HINT: Alter statistics on table column instead. 301ALTER INDEX tbl_idx ALTER COLUMN 2 SET STATISTICS 1000; 302ALTER INDEX tbl_idx ALTER COLUMN 3 SET STATISTICS 1000; 303ERROR: cannot alter statistics on included column "c2" of index "tbl_idx" 304ALTER INDEX tbl_idx ALTER COLUMN 4 SET STATISTICS 1000; 305ERROR: column number 4 of relation "tbl_idx" does not exist 306DROP TABLE tbl; 307/* 308 * 4. CREATE INDEX CONCURRENTLY 309 */ 310CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, UNIQUE(c1, c2) INCLUDE(c3,c4)); 311INSERT INTO tbl SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,1000) AS x; 312CREATE UNIQUE INDEX CONCURRENTLY on tbl (c1, c2) INCLUDE (c3, c4); 313SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 314 indexdef 315--------------------------------------------------------------------------------------------- 316 CREATE UNIQUE INDEX tbl_c1_c2_c3_c4_idx ON public.tbl USING btree (c1, c2) INCLUDE (c3, c4) 317 CREATE UNIQUE INDEX tbl_c1_c2_c3_c4_key ON public.tbl USING btree (c1, c2) INCLUDE (c3, c4) 318(2 rows) 319 320DROP TABLE tbl; 321/* 322 * 5. REINDEX 323 */ 324CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, UNIQUE(c1, c2) INCLUDE(c3,c4)); 325SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 326 indexdef 327--------------------------------------------------------------------------------------------- 328 CREATE UNIQUE INDEX tbl_c1_c2_c3_c4_key ON public.tbl USING btree (c1, c2) INCLUDE (c3, c4) 329(1 row) 330 331ALTER TABLE tbl DROP COLUMN c3; 332SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 333 indexdef 334---------- 335(0 rows) 336 337REINDEX INDEX tbl_c1_c2_c3_c4_key; 338ERROR: relation "tbl_c1_c2_c3_c4_key" does not exist 339SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 340 indexdef 341---------- 342(0 rows) 343 344ALTER TABLE tbl DROP COLUMN c1; 345SELECT indexdef FROM pg_indexes WHERE tablename = 'tbl' ORDER BY indexname; 346 indexdef 347---------- 348(0 rows) 349 350DROP TABLE tbl; 351/* 352 * 7. Check various AMs. All but btree, gist and spgist must fail. 353 */ 354CREATE TABLE tbl (c1 int,c2 int, c3 box, c4 box); 355CREATE INDEX on tbl USING brin(c1, c2) INCLUDE (c3, c4); 356ERROR: access method "brin" does not support included columns 357CREATE INDEX on tbl USING gist(c3) INCLUDE (c1, c4); 358CREATE INDEX on tbl USING spgist(c3) INCLUDE (c4); 359CREATE INDEX on tbl USING gin(c1, c2) INCLUDE (c3, c4); 360ERROR: access method "gin" does not support included columns 361CREATE INDEX on tbl USING hash(c1, c2) INCLUDE (c3, c4); 362ERROR: access method "hash" does not support included columns 363CREATE INDEX on tbl USING rtree(c3) INCLUDE (c1, c4); 364NOTICE: substituting access method "gist" for obsolete method "rtree" 365CREATE INDEX on tbl USING btree(c1, c2) INCLUDE (c3, c4); 366DROP TABLE tbl; 367/* 368 * 8. Update, delete values in indexed table. 369 */ 370CREATE TABLE tbl (c1 int, c2 int, c3 int, c4 box); 371INSERT INTO tbl SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 372CREATE UNIQUE INDEX tbl_idx_unique ON tbl using btree(c1, c2) INCLUDE (c3,c4); 373UPDATE tbl SET c1 = 100 WHERE c1 = 2; 374UPDATE tbl SET c1 = 1 WHERE c1 = 3; 375-- should fail 376UPDATE tbl SET c2 = 2 WHERE c1 = 1; 377ERROR: duplicate key value violates unique constraint "tbl_idx_unique" 378DETAIL: Key (c1, c2)=(1, 2) already exists. 379UPDATE tbl SET c3 = 1; 380DELETE FROM tbl WHERE c1 = 5 OR c3 = 12; 381DROP TABLE tbl; 382/* 383 * 9. Alter column type. 384 */ 385CREATE TABLE tbl (c1 int,c2 int, c3 int, c4 box, UNIQUE(c1, c2) INCLUDE(c3,c4)); 386INSERT INTO tbl SELECT x, 2*x, 3*x, box('4,4,4,4') FROM generate_series(1,10) AS x; 387ALTER TABLE tbl ALTER c1 TYPE bigint; 388ALTER TABLE tbl ALTER c3 TYPE bigint; 389\d tbl 390 Table "public.tbl" 391 Column | Type | Collation | Nullable | Default 392--------+---------+-----------+----------+--------- 393 c1 | bigint | | | 394 c2 | integer | | | 395 c3 | bigint | | | 396 c4 | box | | | 397Indexes: 398 "tbl_c1_c2_c3_c4_key" UNIQUE CONSTRAINT, btree (c1, c2) INCLUDE (c3, c4) 399 400DROP TABLE tbl; 401