为什么在 PostgreSQL 中执行 COPY 时序列没有更新? [英] Why are sequences 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 期间没有增加序列 ids，因为在正常 INSERTing 期间工作正常.不是指示序列 id 设置最大记录数，难道没有任何机制可以教育 COPY 命令在其批量复制选项期间增加序列 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;

你写道:

它在复制期间没有增加序列 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屋！