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

最新推荐文章于 2024-09-26 11:20:08 发布

原创最新推荐文章于 2024-09-26 11:20:08 发布 · 1.3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #postgresql

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

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';