CREATE UNIQUE INDEX IF NOT EXISTS in postgreSQL

You can check, if an index with a given name does exist by this statement.

If your index name is some_table_some_field_idx

SELECT count(*) > 0FROM pg_class cWHERE c.relname = 'some_table_some_field_idx' AND c.relkind = 'i';

Starting from Postgres 9.5 you can even use

CREATE INDEX IF NOT EXISTS

Just another ready-to-use solution.

PostgreSQL v9.0+:

DO $BLOCK$BEGIN    BEGIN        CREATE INDEX index_name ON table_name( column_name );    EXCEPTION        WHEN duplicate_table        THEN RAISE NOTICE 'index ''index_name '' on table_name already exists, skipping';    END;END;$BLOCK$;

PostgreSQL v9.5+:

CREATE INDEX IF NOT EXISTS index_name ON table_name( column_name );

I have wrapped a_horse_with_no_name's code with PLSQL function for more convenient usage. I hope somebody will find it useful.

CREATE OR REPLACE FUNCTION create_index(table_name text, index_name text, column_name text) RETURNS void AS $$ declare    l_count integer;begin  select count(*)     into l_count  from pg_indexes  where schemaname = 'public'    and tablename = lower(table_name)    and indexname = lower(index_name);  if l_count = 0 then      execute 'create index ' || index_name || ' on ' || table_name || '(' || column_name || ')';  end if;end;$$ LANGUAGE plpgsql;

usage: select create_index('my_table', 'my_index_name', 'id');