Hi all,
I've noticed that LIKE tablename INCLUDING INDEXES skips any indexes
that were duplicated.
e.g.
CREATE TABLE people (id int, alias text);
CREATE INDEX idx_people_id_1 ON people (id);
CREATE INDEX idx_people_id_2 ON people (id) WHERE id % 2 = 0;
CREATE INDEX idx_people_alias_1 ON people (alias);
CREATE INDEX idx_people_alias_2 ON people (alias);
CREATE INDEX idx_people_alias_3_tblspc ON people (alias) TABLESPACE ts;
CREATE INDEX idx_people_alias_4 ON people (alias) WITH (FILLFACTOR = 24);
\d+ people
Table "public.people"Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+----------+--------------+-------------id | integer | | plain |
|alias | text | | extended | |
Indexes: "idx_people_alias_1" btree (alias) "idx_people_alias_2" btree (alias) "idx_people_alias_3_tblspc" btree
(alias),tablespace "ts" "idx_people_alias_4" btree (alias) WITH (fillfactor=24) "idx_people_id_1" btree (id)
"idx_people_id_2"btree (id) WHERE (id % 2) = 0
CREATE SCHEMA test;
CREATE TABLE test.people (LIKE people INCLUDING INDEXES);
\d+ test.people
Table "test.people"Column | Type | Modifiers | Storage | Stats target | Description
--------+---------+-----------+----------+--------------+-------------id | integer | | plain |
|alias | text | | extended | |
Indexes: "people_alias_idx" btree (alias) "people_id_idx" btree (id) "people_id_idx1" btree (id) WHERE (id % 2) =
0
As you can see, 3 indexes are missing, which happen to be ones that
would duplicate the column definition of another index. Is this
intentional? If so, shouldn't it be documented behaviour?
--
Thom