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;