postgresql 如何复制表的结构和内容，但顺序不同？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12264719/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to copy structure and contents of a table, but with separate sequence?
提问by Stefan Majewsky
I'm trying to setup temporary tables for unit-testing purposes. So far I managed to create a temporary table which copies the structure of an existing table:
我正在尝试为单元测试目的设置临时表。到目前为止，我设法创建了一个临时表来复制现有表的结构：
CREATE TEMP TABLE t_mytable (LIKE mytable INCLUDING DEFAULTS);
But this lacks the data from the original table. I can copy the data into the temporary table by using a CREATE TABLE ASstatement instead:
但这缺少原始表中的数据。我可以使用CREATE TABLE AS语句将数据复制到临时表中：
CREATE TEMP TABLE t_mytable AS SELECT * FROM mytable;
But then the structure of t_mytablewill not be identical, e.g. column sizes and default values are different. Is there a single statement which copies everything?
但是这样的结构t_mytable不会完全相同，例如列大小和默认值不同。是否有一个语句可以复制所有内容？
Another problem with the first query using LIKEis that the key column still references the SEQUENCEof the original table, and thus increments it on insertion. Is there an easy way to create the new table with its own sequence, or will I have to set up a new sequence by hand?
使用第一个查询的另一个问题LIKE是键列仍然引用SEQUENCE原始表的 ，因此在插入时增加它。是否有一种简单的方法可以使用自己的序列创建新表，还是必须手动设置新序列？
采纳答案by Erwin Brandstetter
Postgres 10 or later
Postgres 10 或更高版本
Postgres 10 introduced IDENTITYcolumnsconforming to the SQL standard (with minor extensions). The ID column of your table would look something like:
Postgres 10 引入了符合 SQL 标准的IDENTITY列（带有少量扩展）。表的 ID 列如下所示：
id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
Syntax in the manual.
Using this instead of a traditional serialcolumn avoids your problem with sequences. IDENTITYcolumns use exclusive, dedicated sequences automatically, even when the specification is copied with LIKE. The manual:
手册中的语法。
使用它代替传统的serial列可以避免序列问题。IDENTITY列自动使用独占的、专用的序列，即使使用LIKE. 手册：
Any identity specifications of copied column definitions will only be copied if
INCLUDING IDENTITYis specified. A new sequence is created for each identity column of the new table, separate from the sequences associated with the old table.
复制的列定义的任何标识规范只有在
INCLUDING IDENTITY指定时才会被复制。为新表的每个标识列创建一个新序列，与与旧表关联的序列分开。
And:
和：
INCLUDING ALLis an abbreviated form ofINCLUDING DEFAULTS INCLUDING IDENTITY INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS.
INCLUDING ALL是 的缩写形式INCLUDING DEFAULTS INCLUDING IDENTITY INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS。
The solutionis simpler now:
现在的解决方案更简单：
CREATE TEMP TABLE t_mytable (LIKE mytable INCLUDING ALL);
INSERT INTO t_mytable TABLE mytable;
SELECT setval(pg_get_serial_sequence('t_mytable', 'id'), max(id)) FROM tbl;
As demonstrated, you can still use setval()to set the sequences current value. A single SELECTdoes the trick. Use pg_get_serial_sequence()to get the name of the sequence.
正如演示的那样，您仍然可以使用setval()来设置序列当前值。单曲SELECT就行。使用pg_get_serial_sequence()来获取序列的名称。
db<>fiddle here
db<>在这里摆弄
Related:
有关的：
- How to reset postgres' primary key sequence when it falls out of sync?
- Is there a shortcut for SELECT * FROM?
- Creating a PostgreSQL sequence to a field (which is not the ID of the record)
Original (old) answer
原始（旧）答案
You can take the create script from a database dump or a GUI like pgAdmin(which reverse-engineers database object creation scripts), create an identical copy (with separate sequence for the serialcolumn), and then run:
您可以从数据库转储或类似pgAdmin（它对数据库对象创建脚本进行逆向工程）的 GUI 中获取创建脚本，创建一个相同的副本（具有单独的serial列序列），然后运行：
INSERT INTO new_tbl
SELECT * FROM old_tbl;
The copy cannot be 100% identical if both tables reside in the same schema. Obviously, the table name has to be different. Index names would conflict, too. Retrieving serial numbers from the same sequence would probably not be in your best interest, either. So you have to (at least) adjust the names.
如果两个表驻留在同一架构中，则副本不能 100% 相同。显然，表名必须不同。索引名称也会发生冲突。从同一序列中检索序列号可能也不符合您的最佳利益。所以你必须（至少）调整名称。
Placing the copy in a different schema avoids all of these conflicts. While you create a temporarytable from a regulartable like you demonstrated, that's automatically the case since temp tables reside in their own temporary schema.
将副本放在不同的模式中可以避免所有这些冲突。当您像演示的那样从常规表创建临时表时，情况会自动发生，因为临时表驻留在它们自己的临时模式中。
Or look at Francisco's answerfor DDL code to copy directly.
或者查看Francisco的答案，直接复制DDL代码。
回答by Francisco Puga
I'm using the following code to do it:
我正在使用以下代码来做到这一点：
CREATE TABLE t_mytable (LIKE mytable INCLUDING ALL);
ALTER TABLE t_mytable ALTER id DROP DEFAULT;
CREATE SEQUENCE t_mytable_id_seq;
INSERT INTO t_mytable SELECT * FROM mytable;
SELECT setval('t_mytable_id_seq', (SELECT max(id) FROM t_mytable), true);
ALTER TABLE t_mytable ALTER id SET DEFAULT nextval('t_my_table_id_seq');
ALTER SEQUENCE t_mytable_id_seq OWNED BY t_mytable.id;
