At what level do Postgres index names need to be unique?

Question

In Microsoft SQL Server and MySQL, index names need to unique within the table, but not within the database. This doesn't seem to be the case for PostgreSQL.

Here's what I'm doing: I made a copy of a table using CREATE TABLE new_table AS SELECT * FROM old_table etc and need to re-create the indexes.

Running a query like CREATE INDEX idx_column_name ON new_table USING GIST(column_name) causes ERROR: relation "idx_column_name" already exists

What's going on here?

Answer 1

Indexes and tables (and views, and sequences, and...) are stored in the pg_class catalog, and they're unique per schema due to a unique key on it:

# \d pg_class       Table "pg_catalog.pg_class"      Column     |   Type    | Modifiers  ----------------+-----------+-----------  relname        | name      | not null  relnamespace   | oid       | not null  ... Indexes:     "pg_class_oid_index" UNIQUE, btree (oid)     "pg_class_relname_nsp_index" UNIQUE, btree (relname, relnamespace) 

Per @wildplasser's comment, you can omit the name when creating the index, and PG will assign a unique name automatically.