Index names in PostgreSQL
- Index names are unique across a single database schema.
- Index names cannot be the same as any other index, (foreign) table, (materialized) view, sequence or user-defined composite type in the same schema.
- Two tables in the same schema cannot have an index of the same name. (Follows logically.)
If you do not care about the name of the index, you can have Postgres auto-name it:
CREATE INDEX ON tbl1 (col1)
is (almost) the same as
CREATE INDEX tbl1_col1_idx ON tbl1 USING btree (col1);
Except that Postgres will avoid a naming collision and automatically pick the next free name:
Just try it. But obviously you would not want to create multiple redundant indexes. So it wouldn't be a good idea to just blindly create a new one.
Test for existence
A very simple way to test is to cast the schema-qualified name to
If it throws an exception, the name is free.
Or, to test the same without throwing an exception, used in a
IF NOT EXISTS (
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname = 'my_name'
AND n.nspname = 'myschema' -- 'public' by default
CREATE INDEX my_name ON myschema.mytable (mycolumn);
This doesn't work for
CREATE INDEX CONCURRENTLY, since that variant cannot be wrapped in an outer transaction. See comment by @Gregory below.
DO statement was introduced with Postgres 9.0. In earlier versions you have to create a function to do the same.
pg_class in the manual.
Basics about indexes in the manual.
You can use the new function
to_regclass() to check without throwing an exception:
Returns NULL if an index (or another object) of that name does not exist. Details:
(To be released in the course of 2015.) Now available:
CREATE INDEX IF NOT EXISTS ...
Also works for
CREATE INDEX CONCURRENTLY. However, per documentation:
Note that there is no guarantee that the existing index is anything
like the one that would have been created.
It's a plain check for the object name.