PostgreSQL 实现查询表字段信息SQL脚本

查询格式：

select
  c.relname 表名称,
 a.attname as 字段名称,
 col_description(a.attrelid,a.attnum) as 注释,
 format_type ( a.atttypid, a.atttypmod ) as 类型,
 case when a.attnotnull='f' then '否' else '是' end as 是否必填,
 a.attnum 序号
from
 pg_class as c,
 pg_attribute as a
where
 a.attrelid = c.oid 
 and a.attnum > 0
 order by c.relname,a.attnum;

查询示例：

select
 c.relname 表名称,
 a.attname as 字段名称,
 split_part(col_description ( a.attrelid, a.attnum ),'：',1) as 注释,
 format_type ( a.atttypid, a.atttypmod ) as 类型,
 case when a.attnotnull='f' then '否' else '是' end as 是否必填,
 a.attnum 序号
from
 pg_class as c,
 pg_attribute as a
where
 c.relnamespace=16389
 and c.relname not like 'v_%'
 and c.relname not like 'pk_%'
 and c.relname not like 'unidx%'
 and c.relname not like '%_index'
 and c.relname not like '%_seq'
 and c.relname not like '%_pkey'
 and a.attrelid = c.oid 
 and a.attnum > 0
 order by c.relname,a.attnum;

查询效果：

补充：postgresql 查询某一个表中的所有字段

postgresql 查询某一个表中的所有字段，也就是查询所有的列名

select * from information_schema.columns
where table_schema='public' and table_name='表名称 ';

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。