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

Question

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

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

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

具体来说，我要找的是:

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

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

---

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

Answer 1

使用系统目录信息功能pg_get_indexdef(index_oid)获取完整信息(包括索引表达式列表)- 在针对 pg_index 的查询中获取给定表的所有索引:

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

相关:

• Django/PostgreSQL varchar to UUID
• Copy indexes from one table to another

如果您依赖于非限定表名(没有架构)，则您依赖于当前的 search_path 设置，并且可能会获得不同架构中同名表的结果。

或者，您可以手动加入 pg_attribute 以获得单独的列，如这些相关答案中所示:

• How to get column attributes query from table name using PostgreSQL?
• Find tables with multiple indexes on same column

关键要素是像这样加入:

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的手册:

This is an array of indnatts values that indicate which table columns this index indexes. For example a value of 1 3 would mean that the first and the third table columns make up the index entries. Key columns come before non-key (included) columns. A zero in this array indicates that the corresponding index attribute is an expression over the table columns, rather than a simple column reference.

添加 AND a.attnum > 0 在技术上不是必需的，因为没有 a.attnum = 0。但它使查询更清晰并且不会造成伤害。 The manual:

Ordinary columns are numbered from 1 up. System columns, such as oid, have (arbitrary) negative numbers.

请注意，“列名列表” 实际上也可以包含表达式。而且自 Postgres 11 以来，还有“包含”列(那里没有表达式)。 pg_get_indexdef() 处理开箱即用的所有可能的并发症。