[大数据]postgresql获取表结构，表名、表注释、字段名、字段类型及长度和字段注释

Postgresql允许向表等对象添加注释。

例如，我使用此SQL命令在表 mytable上添加了注释：

COMMENT ON TABLE mytable IS 'This is my table

查询表注释

所有注释都存储在 pg_description

要获取表中的注释，您可以需要将其加入 pg_class

作为替代方案，您也可以使用函数 obj_description（）来检索以下信息：

select
	relname as tabname,
	obj_description(c.oid)
from
	pg_class c
where
	where   c.relname = 'tablename';

1,查询数据库中所有的表注释

select
	relname as tabname,
	obj_description(c.oid)
from
	pg_class c
where
	obj_description(c.oid) is not null ;

2.查询字段名、字段类型及字段长度和字段注释

select
	a.attnum as "序号",
	c.relname as "表名",
	cast(obj_description(c.oid) as varchar) as "表名描述",
	a.attname as "列名",
	concat_ws('', t.typname, SUBSTRING(format_type(a.atttypid, a.atttypmod) from '\(.*\)')) as "字段类型",
	d.description as "备注"
from
	pg_attribute a
left join pg_description d on
	d.objoid = a.attrelid
	and d.objsubid = a.attnum
left join pg_class c on
	a.attrelid = c.oid
left join pg_type t on
	a.atttypid = t.oid
where
	a.attnum >= 0
	and c.relname like '表名'
order by
	c.relname desc,
	a.attnum asc