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;

查询效果：