postgresql 重复键违反唯一约束

Question

我有一个问题，我知道这个问题已发布多次，但我没有找到问题的答案。问题是我有一个表和一个列“id”，我希望它像往常一样是唯一的数字。这种类型的列是连续的，每次插入后的下一个值来自一个序列，所以一切似乎都很好，但有时仍然会显示此错误。我不知道为什么。在文档中，它说该序列是万无一失的，并且始终有效。如果我向该列添加 UNIQUE 约束会有帮助吗？我以前在 Postres 上工作过很多次，但这个错误是我第一次看到。我一切正常，以前从未遇到过这个问题。您能帮我找到将来可以用于将要创建的所有表的答案吗？假设我们有这样简单的事情:

CREATE TABLE comments
(
  id serial NOT NULL,
  some_column text NOT NULL,
  CONSTRAINT id_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE interesting.comments OWNER TO postgres;

如果我添加:

ALTER TABLE comments ADD CONSTRAINT id_id_key UNIQUE(id)

是否足够或是否还有其他应该做的事情？

Answer 1

This article explains您的序列可能不同步，您必须手动将其恢复同步。

文章摘录以防 URL 更改:

If you get this message when trying to insert data into a PostgreSQLdatabase:

ERROR:  duplicate key violates unique constraint

That likely means that the primary key sequence in the table you'reworking with has somehow become out of sync, likely because of a massimport process (or something along those lines). Call it a "bug bydesign", but it seems that you have to manually reset the a primarykey index after restoring from a dump file. At any rate, to see ifyour values are out of sync, run these two commands:

SELECT MAX(the_primary_key) FROM the_table;   
SELECT nextval('the_primary_key_sequence');

If the first value is higher than the second value, your sequence isout of sync. Back up your PG database (just in case), then run this command:

SELECT setval('the_primary_key_sequence', (SELECT MAX(the_primary_key) FROM the_table)+1);

That will set the sequence to the next available value that's higherthan any existing primary key in the sequence.