postgresql数据库 查询表名、备注及字段、长度、是否可控、是否主键等信息

项目中需要采集postgresql的元数据信息，需查询pgsql 的表名以及表中字段的各信息，

通过查找资料，记之如下：

查询表名及备注SQL

select
    pc.relname as table_name,
    pd.description as comment
from
    pg_catalog.pg_class pc
join pg_catalog.pg_description pd on
    pc.oid = pd.objoid
where
    pc.relname = 'tableName'
    and pd.objsubid = 0

获取指定表的字段名称、长度、是否为空、是否主键等信息

SELECT
    pc.relname AS tableName,
    pa.attname AS columnName,
    pt.typname AS columnType,
    ( 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 
    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