为什么在PostgreSQL中执行COPY时不更新序列？ [英] Why sequences are not updated when COPY is performed in PostgreSQL?

问题描述

我要在PostgreSQL中使用 COPY 语句插入批量记录。我意识到的是，序列ID不会更新，当我稍后尝试插入记录时，它会抛出重复的序列ID。在执行 COPY 之后，是否应该手动更新序列号以获得记录数？执行 COPY 时是否没有解决方案，仅增加序列变量，即表的主键字段？请对此澄清。

I'm inserting bulk records using COPY statement in PostgreSQL. What I realize is, the sequence IDs are not getting updated and when I try to insert a record later, it throws duplicate sequence ID. Should I manually update the sequence number to get the number of records after performing COPY? Isn't there a solution while performing COPY, just increment the sequence variable, that is, the primary key field of the table? Please clarify me on this. Thanks in advance!

例如，如果我插入200条记录， COPY 会很好，并且我的表格会显示所有记录。当我稍后手动插入记录时，它说重复的序列ID错误。很好地暗示它不会在COPYing期间增加序列ID，因为在正常INSERTing期间可以正常工作。除了指示序列ID设置最大记录数外，没有任何机制可以教育 COPY 命令在其批量COPYing选项期间递增序列ID吗？

For instance, if I insert 200 records, COPY does good and my table shows all the records. When I manually insert a record later, it says duplicate sequence ID error. It very well implies that it didn’t increment the sequence ids during COPYing as work fine during normal INSERTing. Instead of instructing the sequence id to set the max number of records, won’t there be any mechanism to educate the COPY command to increment the sequence IDs during its bulk COPYing option?

推荐答案

您问：

是否应该在执行COPY之后手动更新序列号以获取记录数？

是的，您应该，如此处记录的：

在COPY FROM之后更新序列值：

Update the sequence value after a COPY FROM:

| BEGIN;
| COPY distributors FROM 'input_file';
| SELECT setval('serial', max(id)) FROM distributors;
| END;

您输入：

在正常的插入过程中，COPYing期间不会增加序列ID，因为工作正常

但是事实并非如此！ :)当您执行普通的INSERT时，通常不会为SEQUENCE支持的主键指定显式值。如果这样做了，您将遇到与现在相同的问题：

But that is not so! :) When you perform a normal INSERT, typically you do not specify an explicit value for the SEQUENCE-backed primary key. If you did, you would run in to the same problems as you are having now:

postgres=> create table uh_oh (id serial not null primary key, data char(1));
NOTICE:  CREATE TABLE will create implicit sequence "uh_oh_id_seq" for serial column "uh_oh.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "uh_oh_pkey" for table "uh_oh"
CREATE TABLE
postgres=> insert into uh_oh (id, data) values (1, 'x');
INSERT 0 1
postgres=> insert into uh_oh (data) values ('a');
ERROR:  duplicate key value violates unique constraint "uh_oh_pkey"
DETAIL:  Key (id)=(1) already exists.

您的COPY命令当然会提供明确的 id 值，就像上面的示例INSERT。

Your COPY command, of course, is supplying an explicit id value, just like the example INSERT above.

这篇关于为什么在PostgreSQL中执行COPY时不更新序列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！