查看特定表的备注--postgresql

1. 获取字段名、类型、注释、是否为空

SELECT
col_description (a.attrelid, a.attnum) as comment,
format_type (a.atttypid, a.atttypmod) as type,
a.attname as name,
a.attnotnull as notnull
FROM
pg_class as c,
pg_attribute as a
where
c.relname = 'persons'
and a.attrelid = c.oid
and a.attnum > 0
order by
a.attname;

 

2. select * from pg_tables---查询数据库下的所有表名

3. 查看特定表下的特定的字段的备注信息

select
a.attnum,
a.attname,
concat_ws (
'',
t.typname,
SUBSTRING (
format_type (a.atttypid, a.atttypmod)
from
'\(.*\)'
)
) as type,
d.description
from
pg_class c,
pg_attribute a,
pg_type t,
pg_description d
where
c.relname = 'persons'
--and a.attnum > 0
and a.attrelid = c.oid
and a.atttypid = t.oid
and d.objoid = a.attrelid
and d.objsubid = a.attnum;

 

4. 查询所有表的备注信息

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 = 'agency_service'
and position ('_2' in tablename) = 0
)
order by
c.relname,
a.attnum