如何复制表的结构和内容，但有单独的顺序？ [英] How to copy structure and contents of a table, but with separate sequence?

问题描述

我正在尝试设置临时表以进行单元测试。到目前为止，我设法创建了一个临时表来复制现有表的结构：

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);

但这缺少原始表中的数据。我可以使用 CREATE TABLE AS 语句将数据复制到临时表中：

But this lacks the data from the original table. I can copy the data into the temporary table by using a CREATE TABLE AS statement instead:

CREATE TEMP TABLE t_mytable AS SELECT * FROM mytable;

但是 t_mytable 的结构不会相同，例如列大小和默认值不同。

But then the structure of t_mytable will not be identical, e.g. column sizes and default values are different. Is there a single statement which copies everything?

使用 LIKE 的第一个查询的另一个问题是键列仍然引用原始表的 SEQUENCE ，因此在插入时将其递增。是否有一种简单的方法来创建具有其自己序列的新表，还是我必须手动设置一个新序列？

Another problem with the first query using LIKE is that the key column still references the SEQUENCE of 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?

推荐答案

Postgres 10或更高版本

引入了Postgres 10 IDENTITY 列符合SQL标准（带有次要扩展）。表的ID列类似于：

Postgres 10 or later

Postgres 10 introduced IDENTITY columns conforming to the SQL standard (with minor extensions). The ID column of your table would look something like:

id    integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY

手册中的语法。

使用它代替传统的 serial 列避免了序列问题。 IDENTITY 列自动使用排他的专用序列，即使使用 Like 复制了规范也是如此。 手册：

Syntax in the manual.
Using this instead of a traditional serial column avoids your problem with sequences. IDENTITY columns use exclusive, dedicated sequences automatically, even when the specification is copied with LIKE. The manual:

如果指定了 INCLUDING IDENTITY ，则仅复制
复制的列定义的任何标识说明。为新表的每个标识列创建一个新序列
，与与旧表相关的序列
分开。

Any identity specifications of copied column definitions will only be copied if INCLUDING IDENTITY is specified. A new sequence is created for each identity column of the new table, separate from the sequences associated with the old table.

并且：

包含全部是包括默认在内，包括身份，包括约束，包括索引，包括存储，包括注释。

INCLUDING ALL is an abbreviated form of INCLUDING DEFAULTS INCLUDING IDENTITY INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS.

strong>解决方案现在更简单了：

The solution is 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;

如所示，您仍然可以使用 setval（） 来设置序列的当前值。单个 SELECT 可以解决问题。使用 pg_get_serial_sequence（） 来获取序列的名称。

As demonstrated, you can still use setval() to set the sequences current value. A single SELECT does the trick. Use pg_get_serial_sequence() to get the name of the sequence.

db<> fiddle 此处

相关：

• 如何在postgres的主键序列不同步时重置？


• 是否存在SELECT * FROM的快捷方式？


• 创建PostgreSQL序列ce到一个字段（不是记录的ID）

• 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)

您可以从数据库转储或诸如 pgAdmin （对数据库对象创建脚本进行反向工程），创建一个相同的副本（对 serial 列使用单独的序列），然后运行：

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 serial column), and then run:

INSERT INTO new_tbl
SELECT * FROM old_tbl;

如果两个表都位于同一模式中，则副本不能100％相同。显然，表名必须不同。索引名称也将冲突。从同一序列中检索序列号可能也不符合您的最大利益。因此，您（至少）必须调整名称。

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.

将副本放置在其他架构中可以避免所有这些冲突。在像您演示的那样从常规表创建临时表时，由于临时表驻留在它们自己的临时模式中，因此情况自动发生。

Placing the copy in a different schema avoids all of these conflicts. While you create a temporary table from a regular table like you demonstrated, that's automatically the case since temp tables reside in their own temporary schema.

或查看 Francisco的答案以直接复制DDL代码。

Or look at Francisco's answer for DDL code to copy directly.

这篇关于如何复制表的结构和内容，但有单独的顺序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！