Postgresql duplicate primary key

Question

I am woking on PG 9.2.14 on our production server I am facing some random issue.

Let's say its a table named users and id is the primary key in it.

When I am trying to reindex the table it give me following error:-

ERROR:  could not create unique index "users_pkey"
DETAIL:  Key (id)=(339) is duplicated.

When I am trying to fetch user with the 339 id, there is no record. I am not sure if its PG's bug or I am doing something wrong.

Any one have faced such kind of problem?

Answer 1

The exact problem was there were duplicate rows with same id.

I am not sure how it entered the db as there was primary key concern since the table was created.

The solution was I had to drop the primary key constraint

alter table users drop constraint users_pkey;

Then pulled the duplicate record with that id using.

Select * from users where id = 339;

Now it showed me around 4-5 rows with same id, deleted those rows and it worked.

I am not sure if there is primary key constraint on the column why PG don't show duplicate records, if its bug in PG or feature.

Answer 2

Check the database. There is already a duplicate value in the table you want to add an index. Check for null value in the table. Seems like there are multiple rows taking null value in user_id.

Remove duplicates and you will be able to add the index. Happy Coding :)