获取postgresql数据字典

想实现一个通用的元数据，从postgresql入手，在网文的基础上增加了一些字段信息，字段元数据主要包括表名、字段名、字段顺序、字段类型、字段全类型、字段长度、数值字段长度、数值字段精度、字段备注等等。

虽然还可以叠加很多比如索引、外键等等

SELECT
pc.relname AS tableName,
    pa.attname AS columnName,
    pa.attnum AS columnorder,
    pt.typname AS columnType,
    concat_ws(
        '',
        pt.typname,
        SUBSTRING(
            format_type(pa.atttypid, pa.atttypmod) from '\(.*\)'
        )
    ) as 列类型, (CASE WHEN pa.attlen > 0 THEN pa.attlen ELSE pa.atttypmod -
        4 END) AS columnLength,
    pa.attnotnull AS isNullAble, (CASE WHEN(SELECT COUNT( * ) FROM pg_constraint WHERE conrelid =
        pa.attrelid AND conkey[1] = attnum AND contype = 'p') > 0 THEN TRUE ELSE FALSE END) AS isPrimary,
    pd.description AS columnDescription,
    CASE atttypid
      WHEN 21 /*int2*/ THEN 16
      WHEN 23 /*int4*/ THEN 32
      WHEN 20 /*int8*/ THEN 64
      WHEN 1700 /*numeric*/ THEN
    CASE WHEN atttypmod = -1
      THEN null
      ELSE((atttypmod - 4) >> 16) & 65535--calculate the precision
      END
      WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/
      WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/
      ELSE null
      END AS numeric_precision,
    CASE
      WHEN atttypid IN(21, 23, 20) THEN 0
      WHEN atttypid IN(1700) THEN
      CASE
      WHEN atttypmod = -1 THEN null
      ELSE(atttypmod - 4) & 65535--calculate the scale
      END
      ELSE null
      END AS numeric_scale
FROM
  pg_class pc,
  pg_attribute pa,
  pg_type pt,
  pg_description pd
WHERE pc.oid = pa.attrelid
  AND pt.oid = pa.atttypid
  AND pd.objoid = pa.attrelid
  AND pd.objsubid = pa.attnum
  --AND pc.relname = 'table_name'
ORDER BY pc.relname DESC,    pa.attnum ASC

欢迎关注python与大数据分析