pgsql（postgresql） 查询获取字段名，字段类型，长度，主键，非空，自增，默认值，描述

代码
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 = 'live_camerainfo'
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

效果

 

 

*注释为空的字段也能正常显示，能正确显示bit类别长度
————————————————
版权声明：本文为CSDN博主「南瓜微微」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/u014092126/article/details/119678391