pgsql 查询所有表的主键和备注

TaLinBoy

于 2023-03-28 10:47:32 发布

阅读量1.3k

点赞数

CC 4.0 BY-SA版权

文章标签：数据库 postgresql

本文链接：https://blog.youkuaiyun.com/TaLinBoy/article/details/129811345

该SQL查询从pg_constraint、pg_class、pg_attribute、pg_type和pg_stat_all_tables表中获取公共模式(public)中的主键详细信息，包括主键名称(pk_name)、列名(colname)、数据类型(typename)、模式名(schemaname)、表名(table)以及表的注释(comment)。查询条件是约束类型(contype)为主键(p)。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT
   pg_constraint.conname AS pk_name,
   pg_attribute.attname AS colname,
   pg_type.typname AS typename,
   pg_stat_all_tables.schemaname as "schemaname",
   pg_stat_all_tables.relname as "table",
   cast(obj_description(relfilenode,'pg_class') as varchar) as comment
FROM
   pg_constraint
   INNER JOIN pg_class ON pg_constraint.conrelid = pg_class.OID
   INNER JOIN pg_attribute ON pg_attribute.attrelid = pg_class.OID AND pg_attribute.attnum = pg_constraint.conkey [ 1 ]
   INNER JOIN pg_type ON pg_type.OID = pg_attribute.atttypid
   INNER JOIN pg_stat_all_tables ON pg_stat_all_tables.relid = pg_class.OID
WHERE
   pg_stat_all_tables.schemaname = 'public'
   AND pg_constraint.contype = 'p';