PostgreSql查询表索引信息

scanf_s("%s",＆Mr.lonely)

已于 2024-07-10 11:39:32 修改

阅读量308

点赞数 2

分类专栏： postgresql 文章标签： postgresql 数据库

于 2024-07-10 10:44:03 首次发布

本文链接：https://blog.csdn.net/qq_44347658/article/details/140317380

版权

postgresql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

查询索引详细信息如下：

SELECT
    tab.relname as 表名,
    cls.relname as 索引名,
    am.amname as 索引类型,
    CASE WHEN idx.indisprimary = 't' THEN '是' ELSE '否' END AS 是否主键,
    CASE WHEN idx.indisunique = 't' THEN '是' ELSE '否' END AS 是否唯一索引,
    att.attname as 字段名,
    CASE WHEN att.attlen > 0 THEN format_type(att.atttypid, att.atttypmod) ELSE format_type(att.atttypid, NULL) END AS 字段类型,
    des.description as 字段描述
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
INNER JOIN pg_attribute att ON att.attrelid = tab.oid AND att.attnum = ANY(idx.indkey)
LEFT JOIN pg_description des ON des.objoid = tab.oid AND des.objsubid = att.attnum
WHERE
    tab.relname = 'device';