postgresql 获取表字段相关属性_postgre获取表字段

select row_number() over ()                                      as 序号,
       t.column_name                                             as 字段名称,
       d.description                                             as 字段说明,
       pt.typname                                                as 字段类型,
       coalesce(character_maximum_length, numeric_precision, -1) as 字段长度,
       case a.attnotnull when true then '是' else '否' end        as 允许空值
from information_schema.columns t,
     pg_attribute a,
     pg_description d,
     pg_class c,
     pg_type pt,
     pg_namespace pn
where d.objoid = a.attrelid
  and d.objsubid = a.attnum
  and a.attname = t.column_name
  and a.attnum > 0
  and a.atttypid = pt.oid
  and a.attrelid = c.oid
  and c.relnamespace = pn."oid"
  and c.relname = t.table_name
  and pn.nspname = t.table_schema
  and t.table_schema = '模式名称'
  and t.table_name = '表名称';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23