列出PostgreSQL数据库的所有索引名，列名及其表名 [英] List all index names, column names and its table name of a PostgreSQL database

问题描述

要获取列表的所有索引名称，其列名称和PostgreSQL数据库的表名称的查询是什么？

What is the query to get the list all index names, its column name and its table name of a postgresql database?

我试图获取以下列表：通过使用该查询来查询数据库中的所有索引，但是如何获取索引列表，其列名和表名？

I have tried to get the list of all indexes in a db by using this query but how to get the list of indexes, its column names and its table names?

 SELECT *
 FROM pg_class, pg_index
 WHERE pg_class.oid = pg_index.indexrelid
 AND pg_class.oid IN (
     SELECT indexrelid
     FROM pg_index, pg_class
     WHERE pg_class.oid=pg_index.indrelid
     AND indisunique != 't'
     AND indisprimary != 't'
     AND relname !~ '^pg_');

推荐答案

这将输出所有包含详细信息的索引（摘自视图定义）：

This will output all indexes with details (extracted from my view definitions):

SELECT i.relname as indname,
       i.relowner as indowner,
       idx.indrelid::regclass,
       am.amname as indam,
       idx.indkey,
       ARRAY(
       SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)
       FROM generate_subscripts(idx.indkey, 1) as k
       ORDER BY k
       ) as indkey_names,
       idx.indexprs IS NOT NULL as indexprs,
       idx.indpred IS NOT NULL as indpred
FROM   pg_index as idx
JOIN   pg_class as i
ON     i.oid = idx.indexrelid
JOIN   pg_am as am
ON     i.relam = am.oid;

可选地在末尾添加一个额外的连接以修剪命名空间：

Optionally add an extra join to the end so as to trim the namespaces:

SELECT i.relname as indname,
       i.relowner as indowner,
       idx.indrelid::regclass,
       am.amname as indam,
       idx.indkey,
       ARRAY(
       SELECT pg_get_indexdef(idx.indexrelid, k + 1, true)
       FROM generate_subscripts(idx.indkey, 1) as k
       ORDER BY k
       ) as indkey_names,
       idx.indexprs IS NOT NULL as indexprs,
       idx.indpred IS NOT NULL as indpred
FROM   pg_index as idx
JOIN   pg_class as i
ON     i.oid = idx.indexrelid
JOIN   pg_am as am
ON     i.relam = am.oid
JOIN   pg_namespace as ns
ON     ns.oid = i.relnamespace
AND    ns.nspname = ANY(current_schemas(false));

这篇关于列出PostgreSQL数据库的所有索引名，列名及其表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！