postgresql查看表、字段注释
一、查看pg 表字段‘名称’、‘类型’、‘非空’、‘注释’
SELECT a.attname as 字段名, format_type(a.atttypid,a.atttypmod) as 类型, a.attnotnull as 非空, col_description(a.attrelid,a.attnum) as 注释 FROM pg_class as c,pg_attribute as a where a.attrelid = c.oid and a.attnum>0 and c.relname = '你的表名';
二、查看pg 某库 所有‘表名称’、‘字段名称‘以及‘字段注释’和‘字段类型’
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;
-- 查询所有表注释 SELECT tb.table_name, d.description FROM information_schema.tables tb JOIN pg_class c ON c.relname = tb.table_name LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = '0' WHERE tb.table_schema = 'test_schema'; -- 查询所有列注释 SELECT col.table_name, col.column_name, col.ordinal_position AS o, d.description FROM information_schema.columns col JOIN pg_class c ON c.relname = col.table_name LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = col.ordinal_position WHERE col.table_schema = 'test_schema' ORDER BY col.table_name, col.ordinal_position; -- 查询所有没注释的表 SELECT tb.table_name, d.description FROM information_schema.tables tb JOIN pg_class c ON c.relname = tb.table_name LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = '0' WHERE tb.table_schema = 'test_schema' AND d.description IS NULL; -- 查询所有没注释的列 SELECT col.table_name, col.column_name, col.ordinal_position AS o, d.description FROM information_schema.columns col JOIN pg_class c ON c.relname = col.table_name LEFT JOIN pg_description d ON d.objoid = c.oid AND d.objsubid = col.ordinal_position WHERE col.table_schema = 'test_schema' AND description IS NULL ORDER BY col.table_name, col.ordinal_position;