I recently came across a scenario like this (tested on git head):
----
CREATE TABLE test (id int);
CREATE INDEX test_idx1 ON test (id);
CREATE INDEX test_idx2 ON test (id);
CREATE TABLE test_copycat (LIKE test INCLUDING ALL);
\d test_copycat
----
Why do we end up with only one index on test_copycat? The culprit
seems to be transformIndexConstraints(), which explains:
* Scan the index list and remove any redundant index specifications. This * can happen if, for instance, the user
writesUNIQUE PRIMARY KEY. A * strict reading of SQL92 would suggest raising an error instead, but * that strikes me
astoo anal-retentive. - tgl 2001-02-14
and this code happily throws out the second index statement in this
example, since its properties are identical to the first. (Side note:
some index properties, such as tablespace specification and comment,
are ignored when determining duplicates). This behavior does seem like
a minor POLA violation to me -- if we do not forbid duplicate indexes
on the original table, it seems surprising to do so silently with
INCLUDING INDEXES.
There was consideration of similar behavior when this patch was
proposed[1], so perhaps the behavior is as-designed, and I guess no
one else has complained. IMO this behavior should at least be
documented under the "LIKE source_table" section of CREATE TABLE's doc
page.
Josh
[1] http://archives.postgresql.org/pgsql-patches/2007-07/msg00173.php