Postgres 10或更高版本
Postgres 10引入了符合SQL标准(带有轻微扩展)的IDENTITY
列。您的表的ID列将类似于:
id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY
手册中的语法。
使用这个而不是传统的serial
列避免了您在序列方面的问题。IDENTITY
列自动使用独占的专用序列,即使使用LIKE
复制规范也是如此。 手册:
如果指定了INCLUDING IDENTITY
,则复制的列定义中的任何标识规范都将被复制。为新表的每个标识列创建一个新序列,与旧表关联的序列不同。
并且:
INCLUDING ALL
是 INCLUDING DEFAULTS INCLUDING IDENTITY INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS
的简写形式。
现在解决方案更简单了:
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()
6可获取序列的名称。
db<>fiddle 在此处查看
相关:
原始(旧)答案
您可以从数据库转储或类似pgAdmin的GUI中获取创建脚本(反向工程数据库对象创建脚本),创建一个完全相同的副本(带有单独的序列用于serial
列),然后运行:
INSERT INTO new_tbl
SELECT * FROM old_tbl;
如果两个表位于同一个模式中,复制的内容不能完全相同。显然,表名必须不同。索引名称也会冲突。从同一序列中检索序列号可能也不是最好的选择。因此,您必须(至少)调整名称。
将副本放置在不同的模式中可以避免所有这些冲突。当您像您演示的那样从常规表创建临时表时,这自动发生,因为临时表驻留在它们自己的临时模式中。
或者查看Francisco's answer中直接复制的 DDL 代码。
ALTER SEQUENCE t_mytable_id_seq OWNED BY t_mytable.id;
- Erwin Brandstetter