一、查看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;

復(fù)制代碼
-- 查詢所有表注釋
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;
復(fù)制代碼