Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am working on a function that allows me to add an index if it does not exist. I am running into the problem that I cannot get a list of indexes to compare to. Any thoughts?

This is a similar issue to the column creation one that is solved with this code:

share|improve this question
Have you looked at the PostgreSQL documentation at ? You can most likely run a query that will tell you if the index you want is already created. –  Max Vernon Feb 27 '13 at 21:26
Goes without saying that you need to state your version of PostgreSQL for a question like that. –  Erwin Brandstetter Feb 27 '13 at 22:07
This is a good question, I am working in 8.4 in my production –  GuidoS Feb 28 '13 at 17:03

2 Answers 2

up vote 41 down vote accepted

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 regclass:

SELECT 'myschema.myname'::regclass

If it throws an exception, the name is free.
Or, to test the same without throwing an exception, used in a DO statement:

DO $$

    SELECT 1
    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
    ) THEN

    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.

The DO statement was introduced with Postgres 9.0. In earlier versions you have to create a function to do the same.
Details about pg_class in the manual.
Basics about indexes in the manual.

Postgres 9.4

You can use the new function to_regclass() to check without throwing an exception:

SELECT to_regclass('myschema.myname');

Returns NULL if an index (or another object) of that name does not exist. Details:

Postgres 9.5

(To be released in the course of 2015.) Now available:


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.

share|improve this answer
So does this mean that index names are unique through an instance, not by table? –  GuidoS Feb 28 '13 at 13:41
ok, I see now that it's an issue of name spaces, so indexes are unique to the namespace/schema... thanks –  GuidoS Feb 28 '13 at 17:02
While being a great answer, do note that you can't add indexes CONCURRENTLY this way. You'll get ERROR: CREATE INDEX CONCURRENTLY cannot be executed from a function or multi-command string. –  Gregory Goltsov Aug 1 '14 at 11:59
Good news: it will be available in 9.5. Here is actual commit… –  Sergey Mirvoda Apr 9 at 7:13
Note that to_regclass returns NULL if the index does not exist, whereas querying pg_class returns an empty result set. –  Sean the Bean May 27 at 23:39

protected by Paul White Jul 22 at 6:19

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.