PostgreSQL 实现查询表字段信息SQL脚本

SELECT

   c.relname 表名称,

  A.attname AS 字段 名称,

  col_description(A.attrelid,A.attnum) AS 注释,

  format_type ( A.atttypid, A.atttypmod ) AS 类型,

  CASE WHEN A.attnotnull= 'f' THEN '否' ELSE '是' END AS 是否必填,

  a.attnum 序号

FROM

  pg_class AS c,

  pg_attribute AS a

WHERE

  A.attrelid = C.oid

  AND A.attnum > 0

  ORDER BY c.relname,a.attnum;

SELECT

  c.relname 表名称,

  A.attname AS 字段名称,

  split_part(col_description ( A.attrelid, A.attnum ), '：' ,1) AS 注释,

  format_type ( A.atttypid, A.atttypmod ) AS 类型,

  CASE WHEN A.attnotnull= 'f' THEN '否' ELSE '是' END AS 是否必填,

  a.attnum 序号

FROM

  pg_class AS c,

  pg_attribute AS a

WHERE

  c.relnamespace=16389

  AND c.relname not like 'v_%'

  AND c.relname not like 'pk_%'

  AND c.relname not like 'unidx%'

  AND c.relname not like '%_index'

  AND c.relname not like '%_seq'

  AND c.relname not like '%_pkey'

  AND A.attrelid = C.oid

  AND A.attnum > 0

  ORDER BY c.relname,a.attnum;

select * from information_schema.columns

where table_schema= 'public' and table_name= '表名称 ' ;