Postgresql Primary Key Sequence Out Of Sync

Photo by cote

I’m not sure how this happened, but I kept getting

PGError: ERROR:  duplicate key violates unique constraint...

messages while doing a simple INSERT using ActiveRecord with a PostgreSQL database, and the key was the primary key for the table. My guess was that the primary key sequence was out of sync.

SELECT MAX(id) FROM posts;

returned 4, but

SELECT nextval('posts_id_seq');

returned 4 as well.

I found two ways to fix it. The first odd way is to run the INSERT statement manually in psql. I don’t know why it works in psql and not through ActiveRecord, but I don’t know why there was a problem in the first place.

The second is to update the sequence manually:

SELECT setval('posts_id_seq', (SELECT MAX(id) FROM posts)+1);

I never tried the second method as the problem hasn’t returned.