autogenerated index name already taken

[qwesda]

I just had a situation, where I wanted to create a second (partial + unique) index for a column that already has an index. Postico autogenerates a name, but since I already created a reqular index for the same column and the partial/unique-ness isn't reflected in the generated index name, the names clash.

Since postgres autogenerates an index name anyway, why submit one at all? Is this not true for all PG versions? Otherwise Postico should check if the name will clash and change the generated name.

[jakob]

Hm. It seems PostgreSQL just appends a number (starting with 1) if there is a name collision.

It's been some time since we added this feature, so I'm not totally sure why Postico provides a name. It could be that some old versions of PostgreSQL required providing an index name.

[qwesda]

yes, the name became optional in Postgres 9.0

1. https://www.postgresql.org/docs/9.0/sql-createindex.html

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ name ] ON table [ USING method ]
    ( { column | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace ]
    [ WHERE predicate ]

2. https://www.postgresql.org/docs/8.4/sql-createindex.html

CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] name ON table [ USING method ]
    ( { column | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
    [ WITH ( storage_parameter = value [, ... ] ) ]
    [ TABLESPACE tablespace ]
    [ WHERE predicate ]

[jakob]

I just realised why we always provide an index name: Because we allow setting a comment on the index when creating. The generated SQL is something like this:

CREATE INDEX "tbl_a_idx" ON "public"."tbl"("a");
COMMENT ON INDEX "public"."tbl"."tbl_a_idx" IS 'This is a comment for the newly generated index';

That won't work with auto-generated index names.