Postgresql查询表字段及约束的SQL（生成数据字典）

Postgresql查询表字段及约束的SQL（只能显示当前数据库下的不含pg_catalog和information_schema模式的其他所有模式和普通表，不含视图、物化视图等内容，不同数据库实例需要各自连接并查询）

select ns.nspname,
cl.relname as table_name,
cast(obj_description(cl.relfilenode,'pg_class') as varchar) as "表注释",
a.attname as column_name,
format_type(a.atttypid,a.atttypmod) as data_type,
(case when a.atttypmod-4>0 then a.atttypmod-4 else 0 end) data_length,
(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='p')>0 then 'Y' else '' end) as "主键约束",
(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='u')>0 then 'Y' else '' end) as "唯一约束",
(case when (select count(*) from pg_constraint where conrelid = a.attrelid and conkey[1]=attnum and contype='f')>0 then 'Y' else '' end) as "外键约束",
(case when a.attnotnull=true then 'Y' else '' end) as nullable,
col_description(a.attrelid,a.attnum) as comment
from pg_attribute a
inner join pg_class cl on a.attrelid=cl.oid
inner join pg_namespace ns on ns.oid = cl.relnamespace
where nspname not in ('pg_catalog','information_schema') and cl.relkind ='r' and format_type(a.atttypid,a.atttypmod) not in ('oid','cid','tid','xid')   
order by nspname,table_name,a.attnum;