postgressql数据库查询数据库中的所有表及表注释、表的字段、类型、注释

更多知识 学习：https://www.processon.com/view/60504b5ff346fb348a93b4fa#map

一、查询Postgres数据库中的所有表信息（表名、备注）

SELECT
	relname AS tabname,
	cast( obj_description ( relfilenode, 'pg_class' ) AS VARCHAR ) AS COMMENT 
FROM
	pg_class c 
WHERE
	relkind = 'r' 
	AND relname NOT LIKE 'pg_%' 
	AND relname NOT LIKE 'sql_%' 
ORDER BY
	relname

注意：过滤掉分表：加条件 and relchecks=0 即可

二、查询Postgres数据库中的表字段名、类型、注释、注释是否为空

SELECT
	col_description ( a.attrelid, a.attnum ) AS COMMENT,
	format_type ( a.atttypid, a.atttypmod ) AS type,
	a.attname AS NAME,
	a.attnotnull AS notnull 
FROM
	pg_class AS c,
	pg_attribute AS a 
WHERE
	c.relname = '表名' 
	AND a.attrelid = c.oid 
	AND a.attnum >0

注意：如果直接复制spl去运行的话，如果不能运行，请把引号改英文的。
;

三、如果一中的方式查询出来没有表的备注信息，那么请使用如下的SQL查询

with tmp_tab as (
    select pc.oid as ooid,pn.nspname,pc.*
      from pg_class pc
           left outer join pg_namespace pn
                        on pc.relnamespace = pn.oid
      where 1=1
       and pc.relkind in ('r')
       and pc.relnamespace = 2200 -- select pn.oid, pn.* from pg_namespace pn where 1=1
       and pc.oid not in (
          select inhrelid
            from pg_inherits
       )
       and pc.relname not like '%peiyb%'
    order by pc.relname
),tmp_desc as (
   select pd.*
     from pg_description pd
    where 1=1
      and pd.objsubid = 0
      --and pd.objoid=168605
)
select t0.*
  from (
        select tab.nspname,
               tab.relname,
               de.description,
               'comment on table '||tab.nspname||'.'||tab.relname||' is '''||de.description||''';'
          from tmp_tab tab
               left outer join tmp_desc de
                            on tab.ooid = de.objoid 
         where 1=1    
        ) t0
 where 1=1
   and t0.description is not null
order by t0.relname   
;