Create index if it does not exist

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

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.5 or newer

Now available:

CREATE INDEX IF NOT EXISTS ...

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 variants for older versions below, too.)

To find existing indexes on the same table for the same column(s):

SELECT pg_get_indexdef(indexrelid)
FROM   pg_index
WHERE  indrelid = 'public.big'::regclass
AND   (indkey::int2[])[:] = ARRAY (
   SELECT attnum
   FROM   unnest('{usr_id, created_at}'::text[]) WITH ORDINALITY i(attname, ord)
   JOIN  (
      SELECT attname, attnum
      FROM   pg_attribute
      WHERE  attrelid = 'public.big'::regclass
      ) a USING (attname)
   ORDER BY ord
   );

Restrictions:

• Only works for columns, not other index expressions.

• Also reports partial indexes (with WHERE clause) and covering indexes (with INCLUDE clause).

• Reports any type of index, not just B-tree indexes.

Study the results (if any) before proceeding, or refine the query to your needs ...

Further reading:

• Find tables with multiple indexes on same column

• Normalize array subscripts for 1-dimensional array so they start with 1

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:

• How to check if a table exists in a given schema

This doesn't work for CREATE INDEX CONCURRENTLY, since that variant cannot be wrapped in an outer transaction. See comment by @Gregory below.
Postgres 9.3 or older

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, use 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
$;

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.

StackExchange Database Administrators Q#35616, answer score: 132