58

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:
https://stackoverflow.com/a/12603892/368511

97

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, 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 collisions and automatically pick the next free name:

tbl1_col1_idx 
tbl1_col1_idx2
tbl1_col1_idx3
...

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

Postgres 9.3 or older

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
$$
BEGIN
   IF NOT EXISTS (
      SELECT
      FROM   pg_class c
      JOIN   pg_namespace n ON n.oid = c.relnamespace
      WHERE  c.relname = 'mytable_mycolumn_idx'
      AND    n.nspname = 'myschema'
   ) THEN

        CREATE INDEX mytable_mycolumn_idx ON myschema.mytable (mycolumn);
    END IF;
END
$$;

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:

DO
$$
BEGIN
   IF to_regclass('myschema.mytable_mycolumn_idx') IS NULL THEN
      CREATE INDEX mytable_mycolumn_idx ON myschema.mytable (mycolumn);
   END IF;

END
$$;

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

Postgres 9.5

Now available:

CREATE INDEX IF NOT EXISTS ...

That also works for CREATE INDEX CONCURRENTLY IF NOT EXISTS.

However, the manual warns:

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. Applies to all variants here.

  • 7
    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. – gregoltsov Aug 1 '14 at 11:59
12

It will be available in 9.5. Here is actual git commit https://github.com/postgres/postgres/commit/08309aaf74ee879699165ec8a2d53e56f2d2e947

Discussion on pg hackers http://postgresql.nabble.com/CREATE-IF-NOT-EXISTS-INDEX-td5821173.html

protected by Paul White Jul 22 '15 at 6:19

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

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.