如果在 postgreSQL 中不存在，则创建唯一索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24674281/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
CREATE UNIQUE INDEX IF NOT EXISTS in postgreSQL
提问by user3429578
Plese I would like to do in PostgreSQL something like
请我想在 PostgreSQL 中做类似的事情
CREATE UNIQUE INDEX IF NOT EXISTS
Any idea?
任何的想法？
回答by Torge
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
如果您的索引名称是 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
从 Postgres 9.5 开始，您甚至可以使用
CREATE INDEX IF NOT EXISTS
回答by volvpavl
Just another ready-to-use solution.
只是另一种即用型解决方案。
PostgreSQL v9.0+:
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+:
PostgreSQL v9.5+：
CREATE INDEX IF NOT EXISTS index_name ON table_name( column_name );
回答by Kragh
I have wrapped a_horse_with_no_name's code with PLSQL function for more convenient usage. I hope somebody will find it useful.
我已经用 PLSQL 函数包装了 a_horse_with_no_name 的代码，以便更方便地使用。我希望有人会觉得它有用。
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');
用法：select create_index('my_table', 'my_index_name', 'id');
回答by a_horse_with_no_name
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;
$$
回答by Joao Esperancinha
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:
如果您仍然停留在以前的版本中，我建议您不要使用计数，而直接在您的 if 条件中使用查询。使代码更简单。你可以尝试这样的事情：
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
$$;
回答by Feki Zied
Another solution that support multiple columns index, based on @Kragh answer
另一个支持多列索引的解决方案，基于@Kragh 的回答
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(,','));
end if;
END;
$$
LANGUAGE plpgsql;
and then you can use it like any other pg function:
然后您可以像使用任何其他 pg 函数一样使用它：
select create_index('events_timestamp_type_idx', 'events', 'timestamp', 'type');
select create_index('events_timestamp_type_idx', 'events', 'timestamp', 'type');
