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

Question

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.

Answer 1

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.

Answer 2

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.