下面是对shekwi的查询稍加改进的版本。 它生成主键约束，并能够处理临时表:

with pkey as
(
    select cc.conrelid, format(E',
    constraint %I primary key(%s)', cc.conname,
        string_agg(a.attname, ', ' 
            order by array_position(cc.conkey, a.attnum))) pkey
    from pg_catalog.pg_constraint cc
        join pg_catalog.pg_class c on c.oid = cc.conrelid
        join pg_catalog.pg_attribute a on a.attrelid = cc.conrelid 
            and a.attnum = any(cc.conkey)
    where cc.contype = 'p'
    group by cc.conrelid, cc.conname
)
select format(E'create %stable %s%I\n(\n%s%s\n);\n',
    case c.relpersistence when 't' then 'temporary ' else '' end,
    case c.relpersistence when 't' then '' else n.nspname || '.' end,
    c.relname,
    string_agg(
        format(E'\t%I %s%s',
            a.attname,
            pg_catalog.format_type(a.atttypid, a.atttypmod),
            case when a.attnotnull then ' not null' else '' end
        ), E',\n'
        order by a.attnum
    ),
    (select pkey from pkey where pkey.conrelid = c.oid)) as sql
from pg_catalog.pg_class c
    join pg_catalog.pg_namespace n on n.oid = c.relnamespace
    join pg_catalog.pg_attribute a on a.attrelid = c.oid and a.attnum > 0
    join pg_catalog.pg_type t on a.atttypid = t.oid
where c.relname = :table_name
group by c.oid, c.relname, c.relpersistence, n.nspname;

使用table_name参数指定表的名称。

我能想到的最简单的方法是安装pgAdmin 3(在这里找到)，并用它来查看数据库。它将自动生成一个查询，该查询将创建所讨论的表。

如果你有PgAdmin4，那么打开它。转到您的数据库——>模式——>表——>右击要创建脚本的表名——> Scripts——> create script

如果您不想创建函数，而只想让查询创建一个基本的表结构，这里有一个解决方案。

select 'CREATE TABLE ' || table_name ||'(' ||STRING_AGG (
    column_name || ' ' || data_type ,
        ','
       ORDER BY
        table_name,
        ordinal_position
    ) ||');'
    from 
information_schema.columns 
where table_schema = 'public'
group by 
table_name

生成创建特定表背后的SQL (DDL)。 我们可以简单地使用这个SQL查询-

SHOW TABLE your_schema_name.your_table_name

下面是一条语句，它将为指定模式(包括约束)中的单个表生成DDL。

SELECT 'CREATE TABLE ' || pn.nspname || '.' || pc.relname || E'(\n' ||
   string_agg(pa.attname || ' ' || pg_catalog.format_type(pa.atttypid, pa.atttypmod) || coalesce(' DEFAULT ' || (
                                                                                                               SELECT pg_catalog.pg_get_expr(d.adbin, d.adrelid)
                                                                                                               FROM pg_catalog.pg_attrdef d
                                                                                                               WHERE d.adrelid = pa.attrelid
                                                                                                                 AND d.adnum = pa.attnum
                                                                                                                 AND pa.atthasdef
                                                                                                               ),
                                                                                                 '') || ' ' ||
              CASE pa.attnotnull
                  WHEN TRUE THEN 'NOT NULL'
                  ELSE 'NULL'
              END, E',\n') ||
   coalesce((SELECT E',\n' || string_agg('CONSTRAINT ' || pc1.conname || ' ' || pg_get_constraintdef(pc1.oid), E',\n' ORDER BY pc1.conindid)
            FROM pg_constraint pc1
            WHERE pc1.conrelid = pa.attrelid), '') ||
   E');'
FROM pg_catalog.pg_attribute pa
JOIN pg_catalog.pg_class pc
    ON pc.oid = pa.attrelid
    AND pc.relname = 'table_name'
JOIN pg_catalog.pg_namespace pn
    ON pn.oid = pc.relnamespace
    AND pn.nspname = 'schema_name'
WHERE pa.attnum > 0
    AND NOT pa.attisdropped
GROUP BY pn.nspname, pc.relname, pa.attrelid;