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?

Names are unique within the schema. (schema := namespace for tables and constraints (and functions,etc). (though cross-schema-constraints are allowed)) — wildplasser, Dec 05 '14 at 00:04
BTW: is this the actual error message? ( `idx_column_name` is different from `idx_pickup_geom` ) — wildplasser, Dec 05 '14 at 00:13
Indexes share the same namespace ( :=schema) with tables. (index := table). You'll need to invent another name (or omit it: the system can invent a name for you) — wildplasser, Dec 05 '14 at 00:20
@wildplasser thanks for the explanation and suggestion. I gave it a +1. Want to add it as an answer? — Martin Burch, Dec 05 '14 at 00:33
Related: http://stackoverflow.com/questions/13752885/how-to-create-sequence-if-not-exists/13753955#13753955 — Erwin Brandstetter, Dec 05 '14 at 01:28

Denis de Bernardy · Accepted Answer · 2014-12-05T01:14:38.800

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.

score 9 · Answer 2 · edited May 24 '17 at 10:58

9

Names are unique within the schema. A schema is basically a namespace for {tables,constraints}, (and indexes, functions,etc).
cross-schema-constraints are allowed
Indexes share their namespace ( :=schema) with tables. (for Postgres: an index is a table).
(IIRC) the SQL standard does not define indexes; use constraints whenever you can (The GIST index in the question is probably an exception)
Ergo You'll need to invent another name.
or omit it: the system can invent a name if you dont supply one.
The downside of this: you can create multipe indices with the same definition (their names will be suffixed with _1, _2, IIRC)

edited May 24 '17 at 10:58

Paul Grimshaw

19,894
6
40
59

answered Dec 05 '14 at 00:56

wildplasser

43,142
8
66
109

It's not about the namespace (:= schema). If memory serves, it's about constraints on `pg_class`. :-) – Denis de Bernardy Dec 05 '14 at 00:57
IIRC (-;) ,the catalogs only use numeric ID. (which is PK) the namespace/class is functionally dependant. Other (candidate) keys may exist, such as {schema, table}, or even {objecttype, schema, name} – wildplasser Dec 05 '14 at 01:00
@wildplasser To postgresql an index is a relation, **not** a table. A relation is "most everything that has columns or is otherwise similar to a table" (from the http://www.postgresql.org/docs/9.3/static/catalog-pg-class.html doc), including indexes, views, sequences, etc. – Patrick Dec 05 '14 at 02:19

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

2 Answers2

Linked