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
– wildplasser, Commented Dec 5, 2014 at 0:04
BTW: is this the actual error message? ( idx_column_name is different from idx_pickup_geom ) — wildplasser
– wildplasser, Commented Dec 5, 2014 at 0: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
– wildplasser, Commented Dec 5, 2014 at 0:20
@wildplasser thanks for the explanation and suggestion. I gave it a +1. Want to add it as an answer? — Martin Burch
– Martin Burch, Commented Dec 5, 2014 at 0:33

Denis de Bernardy · Accepted Answer · 2014-12-05 01:14:38Z

50

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.

edited Dec 5, 2014 at 1:14

answered Dec 5, 2014 at 0:54

Denis de Bernardy

79.1k14 gold badges138 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paul Grimshaw · Accepted Answer · 2017-05-24 10:58:54Z

11

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, 2017 at 10:58

Paul Grimshaw

21.2k6 gold badges43 silver badges60 bronze badges

answered Dec 5, 2014 at 0:56

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

4 Comments

Denis de Bernardy Over a year ago

It's not about the namespace (:= schema). If memory serves, it's about constraints on pg_class. :-)

wildplasser Over a year ago

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}

Patrick Over a year ago

@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 postgresql.org/docs/9.3/static/catalog-pg-class.html doc), including indexes, views, sequences, etc.

Code4R7 Over a year ago

Actually, constraint names are unique per table: "PostgreSQL does not require constraint names to be unique within a schema (but only per-table)"

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related