Create index if it does not exist

Question

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

Answer 1

Index names in PostgreSQL

• Index names are unique across a single database schema.
• Index names cannot be the same as any other table, view, sequence, user-defined composite type or index 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:

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

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 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);
END IF;

END$$;

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.