postgresql数据库查询所有字段详细信息，包括描述

把里边的public改成相应模式名（默认为public，可不改）

table_name改成对应表名称

  SELECT AA.*,BB.DESC FROM

  (

SELECT

A.ORDINAL_POSITION AS COL_NUM,

A.COLUMN_NAME AS COL_NAME,

A.DATA_TYPE AS COL_TYPE,

A.CHARACTER_MAXIMUM_LENGTH AS COL_LEN,

A.NUMERIC_PRECISION AS COL_PREC,

A.DATETIME_PRECISION AS COL_TIME_PREC,

A.IS_NULLABLE AS COL_NULLABLE,

A.COLUMN_DEFAULT AS COL_DEFAULT

FROM INFORMATION_SCHEMA.COLUMNS A

WHERE A.TABLE_SCHEMA = 'public' AND A.TABLE_NAME = 'table_name'

  )

  AS AA LEFT JOIN

  (

SELECT

OBJSUBID,

DESCRIPTION AS DESC

FROM PG_DESCRIPTION

WHERE OBJOID =

(

  SELECT RELID FROM PG_STAT_ALL_TABLES WHERE SCHEMANAME = 'public' AND RELNAME = 'table_name'

)

  )

  AS BB ON AA.COL_NUM = BB.OBJSUBID ORDER BY AA.COL_NUM;