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:
// Login to psql and run the following
// What is the result?
SELECT MAX(id) FROM your_table;
// Then run...
// This should be higher than the last result.
// If it's not higher... run this to try and fix it. (run a quick pg_dump first...)
SELECT setval('your_table_id_seq', (SELECT MAX(id) FROM your_table));
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.