CREATE UNIQUE INDEX IF NOT EXISTS in postgreSQL

问题:

Plese I would like to do in PostgreSQL something like

CREATE UNIQUE INDEX IF NOT EXISTS

Any idea?

回答1:

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(*) > 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

回答2:

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

回答3:

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');

回答4:

You need some procedural code for this, something like this (untested!):

do
$$
declare 
   l_count integer;
begin
  select count(*)
     into l_count
  from pg_indexes
  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;

end;
$$

回答5:

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:

do 
$$
begin
if not exists (
    select indexname
        from pg_indexes
    where schemaname = 'schemaname'
        and tablename = 'tablename'
        and indexname = 'indexname'
)
then
    create unique indexname (...);
end if;
end 
$$;

回答6:

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;
END;
$$
LANGUAGE plpgsql;

and then you can use it like any other pg function:

select create_index('events_timestamp_type_idx', 'events', 'timestamp', 'type');