PostgreSQL不使用pg_dump如何获取完整建表SQL？支持Knex.js吗

本方案不依赖pg_dump工具，支持指定schema，输出内容包含字段定义、主键约束、索引结构，效果对齐MySQL的show create table命令，同时提供Knex.js适配方式。

纯SQL实现 #

执行以下SQL时，将:schema_name替换为目标schema名（默认通常为public），:table_name替换为目标表名，即可返回完整建表语句：

WITH table_info AS (
    SELECT
        n.nspname AS schema_name,
        c.relname AS table_name,
        c.oid AS table_oid
    FROM pg_class c
    JOIN pg_namespace n ON c.relnamespace = n.oid
    WHERE n.nspname = :schema_name
      AND c.relname = :table_name
      AND c.relkind = 'r'
),
columns_def AS (
    SELECT
        string_agg(
            '  ' || quote_ident(a.attname) || ' ' ||
            pg_catalog.format_type(a.atttypid, a.atttypmod) ||
            CASE WHEN a.attnotnull THEN ' NOT NULL' ELSE '' END ||
            CASE WHEN ad.adbin IS NOT NULL THEN ' DEFAULT ' || pg_catalog.pg_get_expr(ad.adbin, ad.adrelid) ELSE '' END,
            E',\n' ORDER BY a.attnum
        ) AS column_sql
    FROM pg_attribute a
    JOIN table_info ti ON a.attrelid = ti.table_oid
    LEFT JOIN pg_attrdef ad ON a.attrelid = ad.adrelid AND a.attnum = ad.adnum
    WHERE a.attnum > 0 AND NOT a.attisdropped
),
pk_def AS (
    SELECT
        COALESCE(
            E',\n  ' || pg_get_constraintdef(con.oid),
            ''
        ) AS pk_sql
    FROM pg_constraint con
    JOIN table_info ti ON con.conrelid = ti.table_oid
    WHERE con.contype = 'p'
),
indexes_def AS (
    SELECT
        COALESCE(
            string_agg(
                E';\n' || pg_get_indexdef(idx.indexrelid),
                E'\n'
            ),
            ''
        ) AS index_sql
    FROM pg_index idx
    JOIN table_info ti ON idx.indrelid = ti.table_oid
    WHERE NOT idx.indisprimary
)
SELECT
    'CREATE TABLE ' || quote_ident(ti.schema_name) || '.' || quote_ident(ti.table_name) || E' (\n' ||
    cd.column_sql ||
    pk.pk_sql || E'\n);' ||
    id.index_sql AS create_table_sql
FROM table_info ti
CROSS JOIN columns_def cd
CROSS JOIN pk_def pk
CROSS JOIN indexes_def id;

说明：该SQL直接查询PostgreSQL内置系统表生成语句，自动处理标识符转义、字段默认值、非空约束、主键定义、普通索引/唯一索引定义，不会遗漏表结构信息。

Knex.js 实现方式 #

Knex.js没有内置直接输出完整建表语句的API，可以通过两种方式实现需求：

• 直接调用上述纯SQL：通过knex.raw()传入目标schema名、表名执行SQL，直接拿到拼接完成的建表语句，是成本最低的实现方式
• 基于Knex结构探查API自行拼接：先调用knex.schema.withSchema(目标schema).columnInfo(目标表名)获取字段、类型、默认值、非空属性，再通过knex.raw()查询pg_indexes系统表拿到当前表的主键、索引定义，最后按照SQL语法规则拼接为完整建表语句即可。

内容的提问来源于stack exchange，提问作者Aliar