CODEWITHSUNDEEP
ruby-on-railsdatabasepostgresqlruby-on-rails-4
peeyush singla
peeyush singla

Reputation: 577

Postgresql duplicate primary key

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?

Upvotes: 3

Views: 5889

Answers (2)

peeyush singla
peeyush singla

Reputation: 577

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.

Upvotes: 4

Swati Aggarwal
Swati Aggarwal

Reputation: 1275

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 :)

Upvotes: 3

Related Questions