中文版（适用初学）

select
    c.relname as 表名,
    cast(obj_description(c.oid) as varchar) as 表注释,
    a.attnum as 字段序号,
    a.attname as 字段名,
    d.description as 字段注释,
    concat_ws('', t.typname, replace(substring(format_type(a.atttypid, a.atttypmod) from '\(.*\)'), ',0', '')) as 字段类型,
    case
        when a.attnum = any(con.conkey) then '是'
    end as 是否主键,
    case
        when array_position(con.conkey, a.attnum) is not null then concat(array_position(con.conkey, a.attnum))
    end as 主键位置,
    con.conkey 主键所在字段序号,
    case
        when a.attnotnull is true then '是'
        else '否'
    end as 非空,
    case
        when position('::' in col.column_default) > 0 then replace(substring(col.column_default from '.*::'), '::', '')
        else col.column_default
    end as 默认值,
    col.is_identity as 是否自增
from
    pg_class c left join pg_constraint con
        on con.conrelid = c."oid" and con.contype = 'p',
    pg_type t,
    information_schema."columns" col,
    pg_attribute a left join pg_description d
        on d.objoid = a.attrelid and d.objsubid = a.attnum
where
    a.attnum > 0
    and a.attrelid = c.oid
    and a.atttypid = t.oid
    and col.table_name = c.relname
    and col.column_name = a.attname
    and col.table_schema = '你的schema'
    and c.relname = '你的表名'
order by
    主键位置,
    字段序号;

英文版（适合写代码）

select
    c.relname as table_name,
    cast(obj_description(c.oid) as varchar) as table_desc,
    a.attnum as field_seq,
    a.attname as field_name,
    d.description as field_desc,
    concat_ws('', t.typname, replace(substring(format_type(a.atttypid, a.atttypmod) from '\(.*\)'), ',0', '')) as field_type,
    case
        when a.attnum = any(con.conkey) then '是'
        end as is_pk,
    case
        when array_position(con.conkey, a.attnum) is not null then concat(array_position(con.conkey, a.attnum))
        end as at_pk_index,
    con.conkey as pk_field_seqs,
    case
        when a.attnotnull is true then '是'
        else '否'
        end as not_null,
    case
        when position('::' in col.column_default) > 0 then replace(substring(col.column_default from '.*::'), '::', '')
        else col.column_default
        end as default_value,
    col.is_identity as is_identity
from
    pg_class c left join pg_constraint con
        on con.conrelid = c."oid" and con.contype = 'p',
    pg_type t,
    information_schema."columns" col,
    pg_attribute a left join pg_description d
        on d.objoid = a.attrelid and d.objsubid = a.attnum
where
      a.attnum > 0
  and a.attrelid = c.oid
  and a.atttypid = t.oid
  and col.table_name = c.relname
  and col.column_name = a.attname
  and col.table_schema = '{schema}'
  and c.relname = '{table_name}'
order by
    at_pk_index,
    field_seq;