如何获取索引的简单描述？以及如何编写类似SQL Server的查询以获取PostgreSQL数据库的关系型索引完整列表

嘿，我来帮你搞定这两个PostgreSQL索引查询的需求！

需求一：获取索引简单描述的查询语句 #

如果只需要快速查看索引的基础信息（模式、表、索引名和定义），这个简单查询就够用了：

SELECT 
    schemaname, 
    tablename, 
    indexname, 
    indexdef
FROM pg_indexes
WHERE schemaname NOT IN ('pg_catalog', 'information_schema');

这个查询会返回所有非系统模式下的索引信息，indexdef字段直接给出索引的创建语句，足够清晰描述每个索引的结构。

需求二：转换SQL Server查询到PostgreSQL（普通关系型索引，按列拆分） #

当然可以！PostgreSQL的系统表结构和SQL Server不同，但我们可以写出逻辑完全对应的查询，只返回btree类型的普通索引（排除GIN、GIST、BRIN等特殊索引），并且每个索引列单独占一行。

对应的PostgreSQL查询语句如下：

SELECT
    s.nspname AS TABLE_SCHEMA,
    t.relname AS TABLE_NAME,
    idx.relname AS INDEX_NAME,
    c.attname AS COLUMN_NAME,
    pos AS POSITION,
    CASE opt
        WHEN 0 THEN 'ASC'
        WHEN 1 THEN 'DESC'
        ELSE 'UNKNOWN'
    END AS COLUMN_ORDER
FROM
    pg_namespace s
JOIN pg_class t ON s.oid = t.relnamespace
JOIN pg_class idx ON t.oid = idx.indrelid
JOIN pg_index i ON idx.oid = i.indexrelid
JOIN pg_am am ON idx.relam = am.oid
JOIN LATERAL unnest(i.indkey, i.indoption) WITH ORDINALITY AS cols(attnum, opt, pos) ON true
JOIN pg_attribute c ON t.oid = c.attrelid AND c.attnum = cols.attnum
WHERE
    t.relkind = 'r' -- 仅匹配普通表
    AND idx.relkind = 'i' -- 仅匹配索引对象
    AND am.amname = 'btree' -- 过滤出普通btree索引，排除特殊类型
    AND c.attnum > 0 -- 排除系统内部列
    AND s.nspname NOT IN ('pg_catalog', 'information_schema') -- 排除系统模式
ORDER BY
    TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, POSITION;

关键对应说明（和你提供的SQL Server查询对比）： #

• SQL Server的sys.schemas → PostgreSQL的pg_namespace（存储模式信息）
• SQL Server的sys.tables → PostgreSQL的pg_class（relkind='r'表示普通表）
• SQL Server的sys.indexes → PostgreSQL的pg_class（relkind='i'表示索引）+ pg_index（存储索引的具体属性）
• SQL Server的sys.index_columns → 用unnest(i.indkey, i.indoption)模拟，因为PostgreSQL用数组存储索引列和排序选项，展开后每行对应一个索引列
• SQL Server的sys.columns → PostgreSQL的pg_attribute（存储表列信息）
• IC.is_included_column = 0 → 这里自动排除了包含列，因为我们只展开了indkey数组（索引键列），包含列存储在indinclcolumns数组中，不会被包含进来
• X.type BETWEEN 1 AND 2 → 对应am.amname = 'btree'，因为SQL Server的这两种索引类型本质都是btree结构，PostgreSQL中btree是默认的普通关系型索引，正好排除你提到的特殊索引类型

内容的提问来源于stack exchange，提问作者SQLpro