pg库查询表结构字段信息的sql

SELECT 
  (SELECT 
    col_description (a.attrelid, a.attnum) 
  FROM
    pg_class AS c -- 表
    JOIN pg_attribute AS a 
      ON a.attrelid = c.oid -- 字段，字段描述
  WHERE c.relname = table_name 
    AND a.attnum > 0 
    AND a.attname = column_name) 字段描述,
  column_name 字段名称,
  CASE
    WHEN udt_name = 'int8' 
    THEN data_type 
    WHEN udt_name = 'int4' 
    THEN 'int' 
    WHEN udt_name = 'numeric' 
    THEN 'decimal(' || numeric_precision || ',' || numeric_scale || ')' 
    WHEN udt_name = 'timestamp' 
    THEN 'datetime' 
    WHEN udt_name = 'varchar' 
    THEN udt_name || '(' || character_maximum_length || ')' 
    WHEN udt_name = 'bpchar' 
    THEN 'char(1)' 
    ELSE udt_name 
  END 字段类型,
  CASE
    WHEN column_default LIKE '%::%' 
    THEN SUBSTRING(
      column_default,
      0,
      POSITION('::' IN column_default)
    ) 
    ELSE column_default 
  END 默认值 
FROM
  information_schema."columns" c 
WHERE table_name ='表名' ;