Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

You can check, if an index with a given name exists with this statement.

If your index name is some_table_some_field_idx

SELECT count(*) > 0
FROM pg_class c
WHERE 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
    EXCEPTION
        WHEN duplicate_table
        THEN RAISE NOTICE 'index ''index_name '' on table_name already exists, skipping';
$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;
$$ LANGUAGE plpgsql;
usage:
    select create_index('my_table', 'my_index_name', 'id');
                btw, it is a pity that CREATE INDEX IF NOT EXISTS syntax was not added with CREATE TABLE IF NOT EXISTS (added in PG 9.1)!
– Kragh
                Sep 24, 2014 at 9:11
  where schemaname = 'public'
    and tablename = 'your_table'
    and indexname = 'your_index_name';
  if l_count = 0 then 
     execute 'create unique index public.your_index_name on public.your_table(id)';
  end if;
                The problem is that indexname, though unique (part of candidate key) is not necesarily descriptive; a join of pg_indexes.indkey[] and pg_attribute would be needed to find a similar index with a different name.
– joop
                Jul 10, 2014 at 11:44
                @joop: I agree but then, a create index if not exists ... (if there was such a syntax) wouldn't check that either.
– a_horse_with_no_name
                Jul 10, 2014 at 12:23
                It would need a syntax hook ala create index [aa] on bb(cc,dd) WHERE NOT EXISTS ( select ... FROM catalogs xx WHERE (something with {bb,cc,dd} ) ); And there still is the possible problem with unique/non-unique indices sharing the same {bb,cc,dd} values. And index-methods...
– joop
                Jul 10, 2014 at 13:38
If you are still stuck in previous versions, I would recommend not using count, but just the query directly in your if condition. Makes the code simpler. You can try something like this:
begin
if not exists (
    select indexname
        from pg_indexes
    where schemaname = 'schemaname'
        and tablename = 'tablename'
        and indexname = 'indexname'
    create unique indexname (...);
end if;
Another solution that support multiple columns index, based on @Kragh answer
CREATE or replace FUNCTION create_index(_index text, _table text, VARIA
DIC param_args text[]) RETURNS void AS
declare 
   l_count integer;
begin
    select count(*) into l_count
    from pg_indexes
    where schemaname = 'public'
        and tablename = lower(_table)
        and indexname = lower(_index);
    if l_count = 0 then
        EXECUTE format('create index %I on %I (%s)', _index, _table, array_to_string($3,','));
    end if;
LANGUAGE plpgsql;
and then you can use it like any other pg function:
select create_index('events_timestamp_type_idx', 'events', 'timestamp', 'type');
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.