检查PostgreSQL中是否存在索引 [英] Checking for existence of index in PostgreSQL

问题描述

我知道如何创建索引

CREATE INDEX ix_dsvtable
  ON public."DsVTable"
  USING btree
  (dind, sec, regind, datind);

如何检查索引是否已经存在?

And how can I check if index already exists?

我需要检查它们的存在并创建它们(如果还不存在).

I need to check their existence and create them if they don't exist yet.

推荐答案

您可以使用以下查询获取索引列表，它们的表和列:

You can get the list of indexes, their table and column using this query:

select
    t.relname as table_name,
    i.relname as index_name,
    a.attname as column_name
from
    pg_class t,
    pg_class i,
    pg_index ix,
    pg_attribute a
where
    t.oid = ix.indrelid
    and i.oid = ix.indexrelid
    and a.attrelid = t.oid
    and a.attnum = ANY(ix.indkey)
    and t.relkind = 'r'
   -- and t.relname like 'mytable'
order by
    t.relname,
    i.relname;

从那里，您可以按索引名称或所涉及的列检查是否存在，并决定创建/跳过索引.

From there, you can check existence by index name or involved column(s) and decide to create/skip the index.

这篇关于检查PostgreSQL中是否存在索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！