Fixing auto-increment sequences in PostgreSQL

Exporting from SQLite3 and other database-sources and importing them into PostgreSQL might generate some head-scratching errors when you’re trying to insert new rows. You might see an error like this one: “ERROR: duplicate key value violates unique constraint “posts_pkey”“. The error indicates that it’s trying to insert a row into the database with an ID that already exists. Why does this happen? PostgreSQL seems to use something called a “sequence” which we can view and edit using functions like setval and nextval . Anyway, the quick fix is provided by an answer from a thread at Stackoverflow:

UPDATED: Removed the incrementation of MAX(ID) by one. Setval sets the value to the current id so when a new ID is stored nextval will return id + 1.