一、查看pg 表字段‘名称’、‘类型’、‘非空’、‘注释’

1SELECT 
2    a.attname as 字段名,
3    format_type(a.atttypid,a.atttypmod) as 类型, 
4    a.attnotnull as 非空, col_description(a.attrelid,a.attnum) as 注释   
5FROM 
6    pg_class as c,pg_attribute as a 
7where 
8    a.attrelid = c.oid 
9    and 
10    a.attnum>0 
11    and 
12    c.relname = '你的表名';

二、查看pg 某库 所有‘表名称’、‘字段名称‘以及‘字段注释’和‘字段类型’

1select 
2  c.relname 表名,
3  cast(obj_description(relfilenode,'pg_class') as varchar) 名称,
4  a.attname 字段,
5  d.description 字段备注,
6  concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as 列类型 
7from 
8  pg_class c,
9  pg_attribute a,
10  pg_type t,
11  pg_description d
12where 
13  a.attnum>0 
14  and 
15  a.attrelid=c.oid 
16  and 
17  a.atttypid=t.oid 
18  and 
19  d.objoid=a.attrelid 
20  and 
21  d.objsubid=a.attnum
22  and 
23  c.relname in (
24    select 
25	tablename 
26	from 
27	pg_tables 
28	where 
29	schemaname='public' 
30	and 
31	position('_2' in tablename)=0
32	) 
33order by c.relname,a.attnum;