如何获取 PostgreSQL 中表上所有索引的列名列表？

我有这个查询来获取表上的索引列表：

SELECT
    ns.nspname as schema_name,
    tab.relname as table_name,
    cls.relname as index_name,
    am.amname as index_type,
    idx.indisprimary as is_primary,
    idx.indisunique as is_unique
FROM
    pg_index idx
INNER JOIN pg_class cls ON cls.oid=idx.indexrelid
INNER JOIN pg_class tab ON tab.oid=idx.indrelid
INNER JOIN pg_am am ON am.oid=cls.relam
INNER JOIN pg_namespace ns on ns.oid=tab.relnamespace
WHERE ns.nspname = @Schema AND tab.relname = @Name

它似乎工作正常。但现在我需要查询列列表，并且我无法理解系统视图的工作原理。

具体来说，我正在寻找的是：

• [用于匹配第一个查询的索引名称或id]
• 索引中的顺序
• 栏目名称
• 上升或下降
• 排序列或包含列

理想情况下，我希望一次获取给定表的所有索引的上述项目。

请注意，我要查找的不仅仅是列名称。

使用系统目录信息功能pg_get_indexdef(index_oid) https://www.postgresql.org/docs/current/functions-info.html#FUNCTIONS-INFO-CATALOG-TABLE获取完整信息（包括索引表达式列表） - 在查询中pg_index https://www.postgresql.org/docs/current/catalog-pg-index.html获取给定表的所有索引：

SELECT pg_get_indexdef(indexrelid) || ';' AS idx
FROM   pg_index
WHERE  indrelid = 'public.tbl'::regclass;  -- optionally schema-qualified

Related:

• Django/PostgreSQL varchar 到 UUID https://stackoverflow.com/questions/35139818/django-postgresql-varchar-to-uuid/35142957#35142957
• 将索引从一个表复制到另一个表 https://dba.stackexchange.com/a/120194/3684

如果您依赖于非限定表名（没有模式），那么您依赖于当前search_path设置，并且可能会获取不同架构中同名表的结果。

或者，您可以加入pg_attribute手动获取各个列，如这些相关答案中所示：

• 如何使用PostgreSQL从表名获取列属性查询？ https://stackoverflow.com/questions/15928118/how-to-get-column-attributes-query-from-table-name-using-postgresql/15931552#15931552
• 查找同一列上有多个索引的表 https://dba.stackexchange.com/a/204733/3684

关键要素是像这样加入：

FROM   pg_index idx
LEFT   JOIN pg_attribute a ON a.attrelid = idx.indrelid
                          AND a.attnum = ANY(idx.indkey)
                          AND a.attnum > 0

该手册关于pg_index.indkey:

这是一个数组indnatts指示哪些表列的值 该指数的索引。例如，值为1 3就意味着 第一和第三表列组成索引条目。钥匙 列位于非关键（包含）列之前。该数组中有一个零 表示对应的索引属性是一个表达式 表列，而不是简单的列引用。

Adding AND a.attnum > 0技术上没有必要，因为没有a.attnum = 0。但它使查询更清晰，而且不会有什么坏处。手册： https://www.postgresql.org/docs/current/catalog-pg-attribute.html

普通列从 1 开始编号。系统列（例如 oid）具有（任意）负数。

请注意，那里有“列名称列表”实际上也可以包含表达式。从 Postgres 11 开始，还有“包含”列（那里没有表达式）。pg_get_indexdef()开箱即用地处理所有可能的复杂情况。