66

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

105

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.

| improve this answer | |
  • 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

| improve this answer | |

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