PG数据库中查询数据表字段

select
distinct 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_name}'
-- 过滤主键字段
and a.attnum = any(con.conkey)
-- and c.relname = '{table_name}'
order by
at_pk_index,
field_seq;