IntegrityError duplicate key value violates unique constraint - django/postgres
This happend to me - it turns out you need to resync your primary key fields in Postgres. The key is the SQL statement:
SELECT setval('tablename_id_seq', (SELECT MAX(id) FROM tablename)+1);
It appears to be a known difference of behaviour between the MySQL and SQLite (they update the next available primary key even when inserting an object with an explicit id) backends, and other backends like Postgres, Oracle, ... (they do not).
There is a ticket describing the same issue. Even though it was closed as invalid, it provides a hint that there is a Django management command to update the next available key.
To display the SQL updating all next ids for the application MyApp:
python manage.py sqlsequencereset MyApp
In order to have the statement executed, you can provide it as the input for the dbshell management command. For bash, you could type:
python manage.py sqlsequencereset MyApp | python manage.py dbshell
The advantage of the management commands is that abstracts away the underlying DB backend, so it will work even if later migrating to a different backend.
I had an existing table in my "inventory" app and I wanted to add new records in Django admin and I got this error:
Duplicate key value violates unique constraint "inventory_part_pkey" DETAIL: Key (part_id)=(1) already exists.
As mentioned before, I run the code below to get the SQL command to reset the id-s:
python manage.py sqlsequencereset inventory
Piping the python manage.py sqlsequencereset inventory | python manage.py dbshell
to the shell was not working
- So I copied the generated raw SQL command
- Then opened pgAdmin3
https://www.pgadmin.org
for postgreSQL and opened my db - Clicked on the 6. icon (Execute arbitrary SQL queries)
- Copied the statement what was generated
In my case the raw SQL command was:
BEGIN;SELECT setval(pg_get_serial_sequence('"inventory_signup"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "inventory_signup";SELECT setval(pg_get_serial_sequence('"inventory_supplier"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "inventory_supplier";COMMIT;
Executed it with F5.
This fixed everything.