如果不同步，如何重置postgres的主键索引 [英] How to reset postgres' primary key index if it's out of sync

问题描述

这个问题几乎类似于当postgres的主键序列不同步时，如何重置？，除了我没有使用序列，所以那里的解决方案不适用。

This question is almost like How to reset postgres' primary key sequence when it falls out of sync?, with the exception I am not using sequences, so solutions there don't apply.

我有一个这样创建的表：

I have a table created like this:

create table contact (
  name varchar(256) null,
  phone_number varchar(256) constraint phone_number_id primary key,
  email varchar(256) null,
  unit_id int not null references unit(id)
);

以前有很多内容，我已经用

Which used to have lots of content, and I've truncated it with

truncate contact cascade;

现在，尝试输入新数据，前两个输入就好了：

Now, trying to enter new data, first two went in just fine:

> select * from contact;
       name       | phone_number  |          email          | unit_id 
------------------+---------------+-------------------------+---------
 ETUNIMI SUKUNIMI | PUHELIN       | SÄHKÖPOSTI              |       1
 J P              | +3584053xx285 | j.p@xxxxxxxxxxxxxxxxxxx |       2
(2 rows)

但是现在我得到了关于第三个错误

But now I get an error about the third one (which probably was there before the truncate):

> insert into contact(name, email, phone_number, unit_id)
  select 'S S','s.s@xxxxxxx', '+35840xxxx781', id from unit where name = 'Piiritoimisto';
ERROR:  duplicate key value violates unique constraint "phone_number_id"
DETAIL:  Key (phone_number)=(+35840xxxx781) already exists.

我如何告诉PostgreSQL重设约束？将来，我将需要重复此过程，并指示其他人（可能存在错误的数据）重复执行此过程，所以我不想只禁用约束。

How do I tell PostgreSQL to reset the constraint? I will need to repeat this process in the future, and also instruct other people to do it (with data that could be faulty), so I'd rather not like to just disable constraints.

我已经尝试过

REINDEX index phone_number_id;
REINDEX table contact;

，但他们没有帮助。还尝试了 COMMIT; ，它表示没有事务在进行。

but they didn't help. Also tried COMMIT;, and it said there is no transaction going on.

PostgreSQL版本9.3.9，正在运行

PostgreSQL version 9.3.9, running on Ubuntu.

推荐答案

这已通过@a_horse_with_no_name在注释中解决。我在单元表中有一些重复的条目，这导致同时插入多个相同的条目，从而导致错误。主键索引在这里完全不是错误，而是暗示了我的另一个问题。

This was solved by @a_horse_with_no_name in the comments. I had some duplicate entries in unit table, which caused multiple identical entries to be inserted at the same time, causing the error. Primary key index was not at all fault here, instead hinting to my other problem.

将其添加为答案，以便我将其标记为已回答。如果@a_horse_with_no_name将提供他自己的答案，则将其标记为接受的答案，并将其删除。

Adding it as an answer so I can mark this as answered. If @a_horse_with_no_name will provide an answer of his own, I'll mark that as the accepted one and delete this.

这篇关于如果不同步，如何重置postgres的主键索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！