pg库sql语句导出表属性

最新推荐文章于 2024-10-17 15:56:50 发布

原创最新推荐文章于 2024-10-17 15:56:50 发布 · 416 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库

该SQL查询用于获取名为crud_api的表的详细信息，包括表名、列名、非空约束、主键状态、字段类型、长度、自增属性、默认值以及列备注。

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

select
   c.relname as 表名,
   a.attname as 列名,
   (case
       when a.attnotnull = true then 'true'
       else 'false' end) as 非空,
   (case
       when (
       select
           count(pg_constraint.*)
       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 = any(pg_constraint.conkey)
       inner join pg_type on
           pg_type.oid = pg_attribute.atttypid
       where
           pg_class.relname = c.relname
           and pg_constraint.contype = 'p'
           and pg_attribute.attname = a.attname) > 0 then 'true'
       else 'false' end) as 主键,
   concat_ws('', t.typname) as 字段类型,
   (case
       when a.attlen > 0 then a.attlen
       when t.typname='bit' then a.atttypmod
       else a.atttypmod - 4 end) as 长度,
   col.is_identity   as 自增,
   col.column_default   as 默认值,
   (select description from pg_description where objoid = a.attrelid
   and objsubid = a.attnum) as 备注
from
   pg_class c,
   pg_attribute a ,
   pg_type t,
   information_schema.columns as col
where
   c.relname = 'crud_api'
   and a.attnum>0
   and a.attrelid = c.oid
   and a.atttypid = t.oid
   and col.table_name=c.relname and col.column_name=a.attname
order by
   c.relname desc,
   a.attnum asc