postgres查询整个数据库表表名称、字段名称、中文注释，数据量

1、查询数据库所有英文表名称、字段名称及对应的表和字段中文注释、字段类型等，

select c.relname 表名,cast(obj_description(relfilenode,'pg_class') as varchar) 名称,a.attname 字段,d.description 字段备注,concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as 列类型 from pg_class c,pg_attribute a,pg_type t,pg_description d
where a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid and d.objoid=a.attrelid and d.objsubid=a.attnum
and c.relname in (select tablename from pg_tables where schemaname='public' and position('_2' in tablename)=0) order by c.relname,a.attnum 

结果如下：
2、为了把一个表汇总成一行，把表英文名称、中文名称分别单独一列，字段名称汇总成一个单元格，可用如下程序

SELECT aa.表名,aa.名称,string_agg(aa.字段备注,',') as 字段备注xin from (
select c.relname 表名,cast(obj_description(relfilenode,'pg_class') as varchar) 名称,a.attname 字段,d.description 字段备注,concat_ws('',t.typname,SUBSTRING(format_type(a.atttypid,a.atttypmod) from '\(.*\)')) as 列类型 from pg_class c,pg_attribute a,pg_type t,pg_description d
where a.attnum>0 and a.attrelid=c.oid and a.atttypid=t.oid and d.objoid=a.attrelid and d.objsubid=a.attnum
and c.relname in (select tablename from pg_tables where schemaname='public' and position('_2' in tablename)=0) order by c.relname,a.attnum ) aa
group by aa.表名,aa.名称;

查询结果如下

3、查询数据库所有表名称和数据量

SELECT a.relname AS name,
       b.description AS comment,
       a.oid
  FROM pg_class a
       LEFT OUTER JOIN pg_description b ON b.objsubid=0 AND a.oid = b.objoid
 WHERE a.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname='public') --用户表一般存储在public模式下
   AND a.relkind='r'
 ORDER BY a.relname;

我在使用的过程中发现2会有部分表的中文注释空缺的现象，而3不会。解决办法是利用2和3进行查询，把两个结果导出到Excel，利用强大的vlookup函数来解决。PS :Excel还是很强大的，哈哈。。。