PostgreSQL重复键违反唯一约束 [英] postgresql duplicate key violates unique constraint

问题描述

我有一个问题，我知道这个问题已经发布了很多次，但是我没有找到解决问题的答案.问题是我有一个表和一列"id"，我希望它像往常一样是唯一的数字.这种类型的列是串行的，每次插入后的下一个值都是来自一个序列的，因此一切似乎都很好，但有时仍显示此错误.我不知道为什么?在文档中，顺序是傻瓜教授，总是可以正常工作.如果我在该列上添加UNIQUE约束，它将变为thelp吗?我曾经在Postres上工作过很多次，但是这个错误是第一次向我显示.我一切正常，以前从未遇到过这个问题.您能帮我找到将来可以用于所有将要创建的表的答案吗?可以说我们有这样简单的事情:

Hi i have a question i know this was posted many times but i didn't find an answer to my problem. The problem is that i have a table and a column "id" i want it to be unique number just as normal. This type of column is serial and the next value after each insert is comming from a sequence so everything seems to be all right but it still sometimes showing this error. I dont know why ? In the documentation it is writtne the sequence is fool prof and always works. If I add a UNIQUE constraint to that column will it thelp ? I worked before many times on Postres but this error is showing for me for the first time. I did everything as normal and i never had this problem before. Can you help me to find the answer that can be used in the future for all tables that will be created ? Lets say we have something easy like this:

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)

如果足够，还是应该做一些其他事情?

Will if be enought or is there some other thing that should be done ?

推荐答案

This article explains that your sequence might be out of sync and that you have to manually bring it back in sync.

摘录，以防URL更改:

如果在尝试将数据插入PostgreSQL时收到此消息 数据库:

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

ERROR:  duplicate key violates unique constraint

这可能意味着您所使用的表中的主键序列 由于某种原因，与之合作变得不同步 导入过程(或类似方式).称其为臭虫" 设计"，但您似乎必须手动重置主 从转储文件还原后的密钥索引.无论如何，看是否 您的值不同步，请运行以下两个命令:

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

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

如果第一个值大于第二个值，则序列为 不同步.备份您的PG数据库(以防万一)，然后运行thisL

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

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 higher than any existing primary key in the sequence.

这篇关于PostgreSQL重复键违反唯一约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！