DataGrip具有与pgAdmin相同的功能。你可以右键点击一个表，你会看到选项自动生成创建表语句。

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

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

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

你也可以使用一个免费的数据库管理工具，比如DBeaver，它允许你查看表的DDL，这里有一个例子:

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

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

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

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

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

第二个是SQL主机信息

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

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

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

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

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

特点:

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;