How to reset postgres' primary key index if it's out of sync

Question

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.

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.

I've already tried

REINDEX index phone_number_id;
REINDEX table contact;

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

PostgreSQL version 9.3.9, running on Ubuntu.

there used to be 100+ rows, but like you can see from the select statement, there are only two now. — eis, Oct 13 '15 at 14:20
How many rows does `select * from unit where name = 'Piiritoimisto';` return? If that is more than one, then of course you'll get that error because you are inserting the same phone_number for each row returned from the table `unit` — a_horse_with_no_name, Oct 13 '15 at 14:25
@a_horse_with_no_name Thank you. That was it. There were duplicates inserted in that table, even though there shouldn't have been. — eis, Oct 13 '15 at 14:26

score 0 · Accepted Answer · answered Dec 16 '15 at 07:11

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.

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.

How to reset postgres' primary key index if it's out of sync

1 Answers1