我正在开发一个函数,该函数允许我添加不存在的索引。我遇到了无法获取要比较的索引列表的问题。有什么想法吗?

这与创建列的问题类似,可通过以下代码解决:https://stackoverflow.com/a/12603892/368511

评论

您可以尝试:从pg_indexes中选择*,其中schemaname ='[schemaname]'和indexname ='[indexname]'。用适当的值替换[schemaname]和[indexname]。参考:postgresql.org/docs/9.1/static/view-pg-indexes.html

#1 楼

PostgreSQL中的索引名

索引名在单个数据库模式中是唯一的。
索引名不能与任何其他索引,(外部)表,(物化)视图,序列或用户相同同一模式中定义的复合类型。
同一模式中的两个表不能具有相同名称的索引。 (按逻辑顺序。)

如果您不关心索引的名称,请让Postgres自动为其命名: br />
CREATE INDEX ON tbl1 (col1);

除了Postgres会避免命名冲突并自动选择下一个免费名称:
CREATE INDEX tbl1_col1_idx ON tbl1 USING btree (col1);

只需尝试一下。但是,显然,您不想创建多个冗余索引。因此,盲目地创建一个新名称不是一个好主意。
测试存在性
Postgres 9.3或更早版本
一种非常简单的测试方法是强制转换架构限定名称到regclass
tbl1_col1_idx 
tbl1_col1_idx2
tbl1_col1_idx3
...

如果抛出异常,则名称是免费的。
或者,为了在不引发异常的情况下进行测试,请在DO语句中使用:
SELECT 'myschema.myname'::regclass;

这不适用于CREATE INDEX CONCURRENTLY,因为该变体无法包装在外部事务中。请参阅下面@Gregory的评论。
DO语句是Postgres 9.0引入的。在早期版本中,您必须创建一个函数来执行此操作。
手册中有关pg_class的详细信息。
手册中有关索引的基础。
Postgres 9.4
您可以使用新函数to_regclass()进行检查而不会引发异常:
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
$$;

如果该名称的索引(或另一个对象)不存在,则返回NULL。请参阅:

如何检查表是否存在于给定的架构中

Postgres 9.5
现在可用:
DO
$$
BEGIN
   IF to_regclass('myschema.mytable_mycolumn_idx') IS NULL THEN
      CREATE INDEX mytable_mycolumn_idx ON myschema.mytable (mycolumn);
   END IF;

END
$$;

这也有效对于CREATE INDEX CONCURRENTLY IF NOT EXISTS
但是,手册警告:

请注意,不能保证现有索引是任何东西,如将要创建的索引。

这是对对象名称的简单检查。在这里适用于所有变体。

评论


虽然这是一个很好的答案,但请注意,您不能以这种方式同时添加索引。您将得到错误:无法从函数或多命令字符串执行CREATE INDEX CONCURRENTLY。

– Gregoltsov
2014年8月1日在11:59

#2 楼

它将在9.5中可用。
这是实际的git commit https://github.com/postgres/postgres/commit/08309aaf74ee879699165ec8a2d53e56f2d2e947

关于pg黑客的讨论http://postgresql.nabble .com / CREATE-IF-NOT-EXISTS-INDEX-td5821173.html