这是这个老问题的另一个解决方案。多年来，关于这个问题有许多优秀的答案，我的尝试在很大程度上借鉴了它们。

我使用安德烈·列别登科的解决方案作为起点，因为它的输出已经非常接近我的要求。

特点:

following common practice I have moved the foreign key constraints outside the table definition. They are now included as ALTER TABLE statements at the bottom. The reason is that a foreign key can also link to a column of the same table. In that fringe case the constraint can only be created after the table creation is completed. The create table statement would throw an error otherwise. The layout and indenting looks nicer now (at least to my eye) Drop command (commented out) in the header of the definition The solution is offered here as a plpgsql function. The algorithm does however not use any procedural language. The function just wraps one single query that can be used in a pure sql context as well. removed redundant subqueries Identifiers are now quoted if they are identical to reserved postgresql language elements replaced the string concatenation operator || with the appropriate string functions to improve performance, security and readability of the code. Note: the || operator produces NULL if one of the combined strings is NULL. It should only be used when that is the desired behaviour. (check out the usage in the code below for an example)

CREATE OR REPLACE FUNCTION public.wmv_get_table_definition (
    p_schema_name character varying,
    p_table_name character varying
)
    RETURNS SETOF TEXT
    AS $BODY$
BEGIN
    RETURN query 
    WITH table_rec AS (
        SELECT
            c.relname, n.nspname, c.oid
        FROM
            pg_catalog.pg_class c
            LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
        WHERE
            relkind = 'r'
            AND n.nspname = p_schema_name
            AND c.relname LIKE p_table_name
        ORDER BY
            c.relname
    ),
    col_rec AS (
        SELECT
            a.attname AS colname,
            pg_catalog.format_type(a.atttypid, a.atttypmod) AS coltype,
            a.attrelid AS oid,
            ' DEFAULT ' || (
                SELECT
                    pg_catalog.pg_get_expr(d.adbin, d.adrelid)
                FROM
                    pg_catalog.pg_attrdef d
                WHERE
                    d.adrelid = a.attrelid
                    AND d.adnum = a.attnum
                    AND a.atthasdef) AS column_default_value,
            CASE WHEN a.attnotnull = TRUE THEN
                'NOT NULL'
            ELSE
                'NULL'
            END AS column_not_null,
            a.attnum AS attnum
        FROM
            pg_catalog.pg_attribute a
        WHERE
            a.attnum > 0
            AND NOT a.attisdropped
        ORDER BY
            a.attnum
    ),
    con_rec AS (
        SELECT
            conrelid::regclass::text AS relname,
            n.nspname,
            conname,
            pg_get_constraintdef(c.oid) AS condef,
            contype,
            conrelid AS oid
        FROM
            pg_constraint c
            JOIN pg_namespace n ON n.oid = c.connamespace
    ),
    glue AS (
        SELECT
            format( E'-- %1$I.%2$I definition\n\n-- Drop table\n\n-- DROP TABLE IF EXISTS %1$I.%2$I\n\nCREATE TABLE %1$I.%2$I (\n', table_rec.nspname, table_rec.relname) AS top,
            format( E'\n);\n\n\n-- adempiere.wmv_ghgaudit foreign keys\n\n', table_rec.nspname, table_rec.relname) AS bottom,
            oid
        FROM
            table_rec
    ),
    cols AS (
        SELECT
            string_agg(format('    %I %s%s %s', colname, coltype, column_default_value, column_not_null), E',\n') AS lines,
            oid
        FROM
            col_rec
        GROUP BY
            oid
    ),
    constrnt AS (
        SELECT
            string_agg(format('    CONSTRAINT %s %s', con_rec.conname, con_rec.condef), E',\n') AS lines,
            oid
        FROM
            con_rec
        WHERE
            contype <> 'f'
        GROUP BY
            oid
    ),
    frnkey AS (
        SELECT
            string_agg(format('ALTER TABLE %I.%I ADD CONSTRAINT %s %s', nspname, relname, conname, condef), E';\n') AS lines,
            oid
        FROM
            con_rec
        WHERE
            contype = 'f'
        GROUP BY
            oid
    )
    SELECT
        concat(glue.top, cols.lines, E',\n', constrnt.lines, glue.bottom, frnkey.lines, ';')
    FROM
        glue
        JOIN cols ON cols.oid = glue.oid
        LEFT JOIN constrnt ON constrnt.oid = glue.oid
        LEFT JOIN frnkey ON frnkey.oid = glue.oid;
END;
$BODY$
LANGUAGE plpgsql;

如果你想找到一个表的create语句而不使用pg_dump，这个查询可能对你有用(改变'tablename'与你的表被称为什么):

SELECT                                          
  'CREATE TABLE ' || relname || E'\n(\n' ||
  array_to_string(
    array_agg(
      '    ' || column_name || ' ' ||  type || ' '|| not_null
    )
    , E',\n'
  ) || E'\n);\n'
from
(
  SELECT 
    c.relname, a.attname AS column_name,
    pg_catalog.format_type(a.atttypid, a.atttypmod) as type,
    case 
      when a.attnotnull
    then 'NOT NULL' 
    else 'NULL' 
    END as not_null 
  FROM pg_class c,
   pg_attribute a,
   pg_type t
   WHERE c.relname = 'tablename'
   AND a.attnum > 0
   AND a.attrelid = c.oid
   AND a.atttypid = t.oid
 ORDER BY a.attnum
) as tabledefinition
group by relname;

当直接从psql调用时，这样做是有用的:

\pset linestyle old-ascii

另外，这个线程中的generate_create_table_statement函数工作得很好。

下面是一条语句，它将为指定模式(包括约束)中的单个表生成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;

另一个简单的选择是使用[HeidiSQL client][1]作为PostgreSQL数据库。

如何进入列出所有数据库和表的数据库选项卡。

单击想要查看DDL/创建特定表的语句的任何表/视图。

现在这个客户端为你在右边窗口的桌子上做以下工作:

第一个窗口将用于表中的数据

第二个是SQL主机信息

第三是数据库级别的信息，比如哪些表，大小是多少

第四，我们更关心的表/视图信息选项卡将有create table语句随时为您提供。

我不能在快照中显示你在处理机密数据，自己试试，让我知道如果你们发现了任何问题。

这是一个有一些编辑的查询，

select 'CREATE TABLE ' || a.attrelid::regclass::text || '(' ||
string_agg(a.attname || ' ' || pg_catalog.format_type(a.atttypid, 
a.atttypmod)||
        CASE WHEN 
            (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
             FROM pg_catalog.pg_attrdef d
             WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef) IS NOT NULL THEN
            ' DEFAULT '|| (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
                          FROM pg_catalog.pg_attrdef d
                          WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
        ELSE
            '' END
||
        CASE WHEN a.attnotnull = true THEN 
            ' NOT NULL'
        ELSE
            '' END,E'\n,') || ');' 
FROM pg_catalog.pg_attribute a join pg_class on a.attrelid=pg_class.oid
WHERE a.attrelid::regclass::varchar =  
'TABLENAME_with_or_without_schema'
AND a.attnum > 0 AND NOT a.attisdropped  and pg_class.relkind='r'
group by a.attrelid;

一个简单的解决方案，在纯SQL。 您可以将其扩展到您想要显示的更多属性。

with c as (
SELECT table_name, ordinal_position, 
 column_name|| ' ' || data_type col
, row_number() over (partition by table_name order by ordinal_position asc) rn
, count(*) over (partition by table_name) cnt
FROM information_schema.columns
WHERE table_name   in ('pg_index', 'pg_tables')
order by table_name, ordinal_position
)
select case when rn = 1 then 'create table ' || table_name || '(' else '' end
 || col 
 || case when rn < cnt then ',' else '); ' end
from c 
order by table_name, rn asc;

输出:

create table pg_index(indexrelid oid,
 indrelid oid,
 indnatts smallint,
 indisunique boolean,
 indisprimary boolean,
 indisexclusion boolean,
 indimmediate boolean,
 indisclustered boolean,
 indisvalid boolean,
 indcheckxmin boolean,
 indisready boolean,
 indislive boolean,
 indisreplident boolean,
 indkey ARRAY,
 indcollation ARRAY,
 indclass ARRAY,
 indoption ARRAY,
 indexprs pg_node_tree,
 indpred pg_node_tree);

 create table pg_tables(schemaname name,
 tablename name,
 tableowner name,
 tablespace name,
 hasindexes boolean,
 hasrules boolean,
 hastriggers boolean,
 rowsecurity boolean);