PostgreSQL中如何查询指定表的索引名称及索引类型

你当前的SQL只能获取索引名称，要同时拿到索引类型（如PRIMARY、UNIQUE），需要关联PostgreSQL的系统表pg_index和pg_class，因为pg_indexes视图不包含索引类型的信息。

可以使用以下SQL实现需求：

SELECT
    idx.relname AS indexname,
    CASE
        WHEN idx_ind.indisprimary THEN 'PRIMARY'
        WHEN idx_ind.indisunique THEN 'UNIQUE'
        ELSE 'INDEX'
    END AS index_type
FROM
    pg_index idx_ind
JOIN
    pg_class idx ON idx_ind.indexrelid = idx.oid
JOIN
    pg_class tbl ON idx_ind.indrelid = tbl.oid
JOIN
    pg_namespace ns ON tbl.relnamespace = ns.oid
WHERE
    ns.nspname = 'foo'
    AND tbl.relname = 'foo';

说明： #

• pg_index：存储索引的核心属性，其中indisprimary标记是否为主键索引，indisunique标记是否为唯一索引
• pg_class：存储数据库对象（表、索引等）的元数据，通过indexrelid关联到索引对象，indrelid关联到对应的表对象
• pg_namespace：存储模式（schema）信息，用于过滤指定模式下的表

如果需要区分更多索引类型（如GIN、GIST等），可以增加对idx.relam字段的判断，关联pg_am表获取索引访问方法名称：

SELECT
    idx.relname AS indexname,
    CASE
        WHEN idx_ind.indisprimary THEN 'PRIMARY'
        WHEN idx_ind.indisunique THEN 'UNIQUE'
        ELSE am.amname
    END AS index_type
FROM
    pg_index idx_ind
JOIN
    pg_class idx ON idx_ind.indexrelid = idx.oid
JOIN
    pg_class tbl ON idx_ind.indrelid = tbl.oid
JOIN
    pg_namespace ns ON tbl.relnamespace = ns.oid
JOIN
    pg_am am ON idx.relam = am.oid
WHERE
    ns.nspname = 'foo'
    AND tbl.relname = 'foo';

这样就能同时展示索引的约束类型和存储类型了。

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