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

SHOW TABLE your_schema_name.your_table_name

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

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

特点:

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;

如果您想一次对多个表执行此操作，则需要多次使用-t开关(我花了一些时间才弄清楚为什么逗号分隔的列表不起作用)。此外，将结果发送到外文件或管道到另一台机器上的postgres服务器也很有用

pg_dump -t table1 -t table2 database_name --schema-only > dump.sql

pg_dump -t table1 -t table2 database_name --schema-only | psql -h server_name database_name

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

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

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

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

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

第二个是SQL主机信息

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

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

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

在pgadminIII数据库>>schema >>tables>>右键单击“Your table”>>scripts>>“选择任何一个(创建，插入，更新，删除..)”

下面是对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参数指定表的名称。