PostgreSQL 查询表名、表注释、字段名、字段类型、字段默认值、字段备注

查询表名和注释

1
2
3
4
5
6
7
8
9
select
relname as "表名",
cast(obj_description(relname::regclass, 'pg_class') as varchar) as "表注释"
from pg_class
where relname in (
select tablename from pg_tables
where 1 = 1 and tablename like ('%' || 'XX 表' || '%')
)
order by relname asc;

查询表的字段信息

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
select
c.column_name as "字段名",
c.column_default as "默认值",
c.is_nullable as "是否可为空",
c.data_type as "类型",
c.character_maximum_length as "长度",
ad.attnum as "序号",
ad.description as "备注"
from
(
select *
from information_schema.columns where table_name = 'XX 表'
) c
join
(
select
a.attnum,
a.attname,
d.description
from pg_attribute a, pg_description d
where a.attnum > 0 and d.objoid = a.attrelid and d.objsubid = a.attnum
and a.attrelid = (SELECT oid FROM pg_class WHERE relname = 'XX 表')
) ad
on c.column_name = ad.attname;