Postgres如何仅用SELECT与CTE生成指定表CREATE TABLE DDL（无pg_dump/函数）

可以实现，该方案完全基于PostgreSQL系统表的只读查询，仅用CTE和SELECT即可完成，无需创建函数、写入数据或调用外部工具，输出结果与pg_dump --schema-only导出的表结构基本等价。

将下方代码中的'your_schema'替换为目标schema名，'your_table'替换为目标表名，直接执行即可得到完整CREATE TABLE DDL：

WITH table_meta AS (
    SELECT 
        c.oid AS table_oid,
        quote_ident(n.nspname) AS schema_name,
        quote_ident(c.relname) AS table_name
    FROM pg_catalog.pg_class c
    JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
    WHERE n.nspname = 'your_schema' 
      AND c.relname = 'your_table'
      AND c.relkind = 'r' -- 仅匹配普通表
),
column_defs AS (
    SELECT 
        string_agg(
            E'  ' || quote_ident(a.attname) || ' ' || 
            pg_catalog.format_type(a.atttypid, a.atttypmod) ||
            CASE WHEN a.attnotnull THEN ' NOT NULL' ELSE '' END ||
            CASE WHEN a.atthasdef THEN ' DEFAULT ' || pg_catalog.pg_get_expr(d.adbin, d.adrelid) ELSE '' END,
            E',\n'
            ORDER BY a.attnum
        ) AS cols
    FROM pg_catalog.pg_attribute a
    JOIN table_meta t ON a.attrelid = t.table_oid
    LEFT JOIN pg_catalog.pg_attrdef d ON a.attrelid = d.adrelid AND a.attnum = d.adnum
    WHERE a.attnum > 0 -- 过滤系统列
      AND NOT a.attisdropped -- 过滤已删除的列
),
constraint_defs AS (
    SELECT 
        string_agg(
            E'  CONSTRAINT ' || quote_ident(con.conname) || ' ' || pg_catalog.pg_get_constraintdef(con.oid),
            E',\n'
        ) AS cons
    FROM pg_catalog.pg_constraint con
    JOIN table_meta t ON con.conrelid = t.table_oid
    WHERE con.contype IN ('p', 'u', 'c') -- 主键、唯一约束、检查约束
),
table_comment AS (
    SELECT pg_catalog.obj_description(t.table_oid, 'pg_class') AS comment
    FROM table_meta t
)
SELECT 
    'CREATE TABLE ' || schema_name || '.' || table_name || E' (\n' ||
    cols || 
    CASE WHEN cons IS NOT NULL THEN E',\n' || cons ELSE '' END ||
    E'\n);\n' ||
    CASE WHEN comment IS NOT NULL THEN 'COMMENT ON TABLE ' || schema_name || '.' || table_name || ' IS ' || quote_literal(comment) || E';\n' ELSE '' END ||
    -- 拼接列注释
    (SELECT string_agg(
        'COMMENT ON COLUMN ' || schema_name || '.' || table_name || '.' || quote_ident(a.attname) || ' IS ' || quote_literal(pg_catalog.col_description(a.attrelid, a.attnum)) || ';',
        E'\n'
    ) FROM pg_catalog.pg_attribute a JOIN table_meta t ON a.attrelid = t.table_oid WHERE a.attnum > 0 AND NOT a.attisdropped AND pg_catalog.col_description(a.attrelid, a.attnum) IS NOT NULL)
AS create_table_ddl
FROM table_meta, column_defs, constraint_defs, table_comment;

• 以上语句输出包含表结构定义、非空约束、默认值、主键/唯一/检查约束、表注释、列注释，和pg_dump --schema-only的输出核心内容完全一致，仅格式排版存在微小差异
• 若需要额外生成索引、外键、权限配置、所有者设置等内容，可在现有CTE基础上扩展查询pg_index、pg_constraint（筛选contype='f'）、pg_class.relowner等系统表字段即可
• 全程仅执行只读查询，不会对数据库产生任何修改，完全满足无写入权限、无函数创建权限、无shell权限的约束

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