postgresql使用SQL根据schema获取表名、表注释,使用obj_description(relfilenode,'pg_class')获取注释部分为空;
网上大部分使用这个SQL,无法正确获取全部表注释:
select relname as tabname,
cast(obj_description(relfilenode,‘pg_class’) as varchar) as comment from
pg_tables a,pg_class b
where a.tablename = b.relname and a.schemaname ='public'
执行SQL获取结果:
解决方法:
经查看JDBC源码的SQL语句,发现 pg_description的注释更全一些,需要pg_class 和pg_description关联查出注释,
完美解决注释获取不全问题,完整的SQL如下(可自行调整):
select schemaname, tablename,
cast((case when obj_description(relfilenode,'pg_class') is null then d.description else obj_description(relfilenode,'pg_class') end) as varchar ) AS
TABLE_COMMENT
from pg_tables a, pg_class b LEFT JOIN pg_description d ON (b.oid = d.objoid AND d.objsubid = 0)
where a.tablename = b.relname and a.schemaname = 'public' and a.tablename not like 'pg_%' and a.tablename not like 'sql_%' and
a.tablename not like '%_p%' -- 过滤_p分表
and relchecks=0 -- 过滤分表
执行SQL获取结果: