Local postgres db continues to give an error, a duplicate key value violates a unique constraint

Question

I do not understand why postgres raises:

duplicate key value violates unique constraint 

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

VACUUM recommended

The estimated score for a row in a table is significantly different from the actual row.

Why is this happening? Fortunately, this does not happen in production on the hero. This application is for rails.

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, Mar 18, 2012 03:48:37 UTC +00: 00], ["post_id", 62], ["updated_at", Sun, Mar 18, 2012 3:48:37 UTC +00: 00]] PG :: Error: ERROR: duplicate key value violates unique constraint " index_favorites_on_artist_id_and_post_id "DETAILS: Key (artist_id, post_id) = (17, 62) already exists.

But in a real table there is no such record with artist_id = 17 and post_id = 62. But postgres thinks there is.

Answer 1

In fact, 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, then they are not pasted.

Answer 2

You need to run ANALYZE to sync strings. In pgAdmin, right-click the table and select "Maintenance" for this. Then press F5 on the table.

It has nothing to do with a unique key violation. This means that the value you are trying to enter in a column with a UNIQUE or PRIMARY KEY is already on another line.

Answer 3

For error messages related to the PostgreSQL error, ActiveRecord::Base.connection.reset_pk_sequence!('table_name') can help synchronize the key.

Answer 4

A common reason for this is that you put the data in a table with a primary key, which is usually provided by the serial type, but the default sequence () does not synchronize with the table.

Answer 5

I ran into this problem in my development environment . This worked for me phppgadmin->admin->reindex . My solution is dangerous in some situations, as it updates the entire database. I suggest also looking for other solutions when working in a production environment. However, Analyse is a good way to get started, as @Erwin Brandstetter suggested.