Does Postgres only allow one index with the same name?

user_15285537 0
Viewed 416

In MySQL I can have the same index (same name and columns) in different tables. From what I am seeing so far, Postgres only allows me to have one index with a name, even if I try to create it in a different table.

Does Postgres really doesn't allow indexes with the same name or am I doing something wrong?

indexing postgresql
1 Answers

You are correct, and indexes, tables, views and sequences share the same name space, so any two of these objects cannot have the same name if they are in the same schema (= name space).

You can see that from the table definition of the catalog table pg_class, which contains all these objects:

\d pg_class

                     Table "pg_catalog.pg_class"
       Column        │     Type     │ Collation │ Nullable │ Default 
═════════════════════╪══════════════╪═══════════╪══════════╪═════════
 oid                 │ oid          │           │ not null │ 
 relname             │ name         │           │ not null │
[...]
Indexes:
    "pg_class_oid_index" UNIQUE, btree (oid)
    "pg_class_relname_nsp_index" UNIQUE, btree (relname, relnamespace)
    "pg_class_tblspc_relfilenode_index" btree (reltablespace, relfilenode)

The second index pg_class_relname_nsp_index forces the combination of name and schema to be unique.

user_64643080
Related