PostgreSQL查询表以及字段的备注

select
    col.table_catalog,
    col.table_schema,
    col.ordinal_position,
    col.table_name as 表名,
    col.column_name as 字段名,
    col.data_type as 字段类型,
    coalesce(col.character_maximum_length, col.numeric_precision) columnLength,
    col.numeric_scale numericScaleLength,
    des.description as 字段描述
from
    information_schema.columns col
left join pg_description des on
    col.table_name::regclass = des.objoid
    and col.ordinal_position = des.objsubid
where
    table_schema = 'public'
    and table_name like 'table_name'
order by
    col.table_name,
    col.ordinal_position ;

select a.attnum,
       a.attname                                                 columnName,
       t.typname                                                 columnType,
       coalesce(d.character_maximum_length, d.numeric_precision) columnLength,
       d.numeric_scale                                           numericScaleLength,
       (a.attnotnull is false)                                   isNullable,
       d.column_default                                          defaultValue,
       col_description(a.attrelid, a.attnum)          as         columnComment,
       case when length(b.attname) > 0 then 'PRI' end as         columnKey
from pg_class c
         left join pg_namespace p on c.relnamespace = p.oid,
     pg_attribute a,
     pg_type t,
     information_schema.columns d
         left join (select pg_attribute.attname
                    from pg_index,
                         pg_class,
                         pg_attribute
                    where pg_class.oid = 'tablename' :: regclass
                      and pg_index.indrelid = pg_class.oid
                      and pg_attribute.attrelid = pg_class.oid
                      and pg_attribute.attnum = any (pg_index.indkey)) b on d.column_name = b.attname
where a.attrelid = c.oid
  and a.atttypid = t.oid
  and a.attnum > 0
  and c.relname = d.table_name
  and d.column_name = a.attname
  and d.table_schema = 'public'
  and c.relname = 'tablename'