CODEWITHSUNDEEP
ruby-on-railspostgresqlherokuduplicates
Homan
Homan

Reputation: 26718

local postgres db keeps giving error duplicate key value violates unique constraint

I don't understand why postgres is raising: 

duplicate key value violates unique constraint

I went to check the table in pgadmin to see if the table really did have a duplicate and see:

Running VACUUM recommended

The estimated rowcount on the table deviates significantly from the actual rowcount.

Why is this happening? Luckily it doesn't seem to happen in production on heroku. It's a rails app.

Update:

Here is the sql log:

SQL (2.6ms) INSERT INTO "favorites" ("artist_id", "author_id", "created_at", "post_id", "updated_at") VALUES ($1, $2, $3, $4, $5) RETURNING "id" [["artist_id", 17], ["author_id", nil], ["created_at", Sun, 18 Mar 2012 03:48:37 UTC +00:00], ["post_id", 62], ["updated_at", Sun, 18 Mar 2012 03:48:37 UTC +00:00]] PG::Error: ERROR: duplicate key value violates unique constraint "index_favorites_on_artist_id_and_post_id" DETAIL: Key (artist_id, post_id)=(17, 62) already exists.

But in the actual table there is no such record with artist_id = 17 and post_id = 62. But postgres believes that there is.

Upvotes: 4

Views: 6138

Answers (5)

Rais
Rais

Reputation: 69

I faced this problem in my development environment. This worked for me phppgadmin->admin->reindex. My solution is dangerous in some situations as it updates whole database.I suggest to look other solutions as well when working in production environment. However, Analyse is a good way to start as suggested by @Erwin Brandstetter

Upvotes: 0

a learner has no name
a learner has no name

Reputation: 795

In case of PostgreSQL unique key violation error messages, ActiveRecord::Base.connection.reset_pk_sequence!('table_name') might help to bring the key back in sync.

Upvotes: 4

Homan
Homan

Reputation: 26718

actually i think the problem is not related to postgres at all. It was a combination of simultaneous duplicate ajax calls creating what would be a duplicate record, then neither are inserted.

Upvotes: 0

Scott Marlowe
Scott Marlowe

Reputation: 8870

A common cause of this is that you've put data into a table with a primary key that's normally provided by a serial type, but the sequence behind the default() isn't in sync with the table.

Upvotes: 2

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656754

You need to run ANALYZE to get the row count in sync. In pgAdmin right click the table and choose "Maintenance" for that. Then press F5 on the table.

Doesn't have anything to do with the unique key violation, though. That means a value you try to enter in a column with a UNIQUE or PRIMARY KEY constraint is already present in another row.

Upvotes: 9

Related Questions