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

查询表名和注释

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;

查询表的字段信息

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;