PostgreSQL数据库查询某个数据库的表结构（用于Excel导出）

查询将生成一个列表，包含：表名、表备注、列名、字段类型、是否为空、是否主键、是否自增、列备注。用于EXCEL导出写文档使用。

SELECT
    c.table_name AS "表名",
    obj_description(pc.oid) AS "表名备注",
    c.column_name AS "列名",
    -- 拼接字段类型和长度，例如 varchar(50) 或 numeric(10,2)
    CASE
        WHEN c.character_maximum_length IS NOT NULL
            THEN c.udt_name || '(' || c.character_maximum_length || ')'
        WHEN c.numeric_precision IS NOT NULL AND c.numeric_scale IS NOT NULL
            THEN c.udt_name || '(' || c.numeric_precision || ',' || c.numeric_scale || ')'
        ELSE c.udt_name
    END AS "字段类型",
    CASE WHEN c.is_nullable = 'YES' THEN '是' ELSE '否' END AS "是否为空",
    CASE WHEN pk.column_name IS NOT NULL THEN '是' ELSE '否' END AS "是否主键",
    CASE
        -- 兼容旧版 serial/bigserial (通过 default 值判断)
        WHEN c.column_default LIKE 'nextval%' THEN '是'
        -- 兼容新版 PG 10+ 的 identity column
        WHEN c.is_identity = 'YES' THEN '是'
        ELSE '否'
    END AS "是否自增",
    col_description(pc.oid, c.ordinal_position) AS "列备注"
FROM
    information_schema.columns c
-- 关联 pg_class 获取表的 OID (用于获取注释)
INNER JOIN pg_catalog.pg_class pc ON c.table_name = pc.relname
-- 关联 pg_namespace 确保只获取指定 schema 的表
INNER JOIN pg_catalog.pg_namespace pn ON pc.relnamespace = pn.oid AND pn.nspname = c.table_schema
-- 关联查询主键信息
LEFT JOIN (
    SELECT kcu.table_schema, kcu.table_name, kcu.column_name
    FROM information_schema.key_column_usage kcu
    JOIN information_schema.table_constraints tc
      ON kcu.constraint_name = tc.constraint_name
      AND kcu.table_schema = tc.table_schema
    WHERE tc.constraint_type = 'PRIMARY KEY'
) pk ON c.table_schema = pk.table_schema
     AND c.table_name = pk.table_name
     AND c.column_name = pk.column_name
WHERE
    c.table_schema = 'public'  -- 默认为 public schema，如有需要可修改
    AND pc.relkind = 'r'       -- 只查询普通表 (排除视图、索引等)
ORDER BY
    c.table_name,
    c.ordinal_position;