SQL 将表结构复制到新表中

Copy table structure into new table

提问by Alex S

Is there a way to copy the structure of a table into a new table, without data, including all keys and constraints?

有没有办法将表的结构复制到新表中，没有数据，包括所有键和约束？

回答by coderhs

For a simple schema copy use the like clause.

对于简单的模式复制，请使用 like 子句。

CREATE TABLE new_table_name ( like old_table_name including all)

回答by Alex S

Well, the closest you can get with SQL is:

好吧，您可以使用 SQL 获得的最接近的是：

create table new (
    like old
    including defaults
    including constraints
    including indexes
);

But it will not copy everything. The most important things that are missing are FOREIGN KEYs. Also - triggers are also not copied. Not sure about other things.

但它不会复制所有内容。缺少的最重要的东西是外键。此外 - 触发器也不会被复制。不确定其他事情。

Another way is to dump the table structure, change it's name in dump, and load it again:

另一种方法是转储表结构，在转储中更改它的名称，然后再次加载它：

pg_dump -s -t old databases | sed 's/old/new/g' | psql

But beware, that such simplistic sed will also change old to new in other places (for example if you have in your table column named "is_scolded" it will become "is_scnewed").

但请注意，这种简单的 sed 在其他地方也会从旧变为新（例如，如果您的表列中名为“is_scolded”，它将变为“is_scnewed”）。

The question really is rather: why do you need it - because for various purposes, I would use different techniques.

问题实际上是：你为什么需要它 - 因为出于各种目的，我会使用不同的技术。

回答by Dimo Boyadzhiev

To copy a table completely, the short form using the TABLE command can also be used:

要完全复制表，也可以使用使用 TABLE 命令的简短格式：

CREATE TABLE films2 AS
    TABLE films
    WITH NO DATA;

More details here

更多细节在这里

回答by ChssPly76

Take a look at pgAdmin- by far the easiest way to do what you want.
Right-click on table, Scripts - Create.

看看pgAdmin- 迄今为止做你想做的最简单的方法。
右键单击表，脚本 - 创建。

回答by u557022

How about

怎么样

CREATE TABLE sample_table_copy AS (SELECT * FROM sample_table WHERE 1 = 2)

postgresql.org answer

postgresql.org 答案