I recently had to deal with a project that we worked on 2 years ago and we needed to migrate from MySQL to Postgres. We chose to redo some of the database relations in order to get a cleaner and faster code. We had to write custom migration scripts that came with a few consequences.
One of the Postgres errors we encountered was:
ERROR: duplicate key value violates unique constraint “invoice_pkey”
The errors was caused since we wanted to all primary keys in table invoice to be intact and we set the primary key field manually from our migration script in our shiny new Postgres database. The auto-increment was not triggered in the database so the primary key got out of sync with the data in the database.
faisal@ubuntu:~$ sudo su postgres postgres@ubuntu:~$ psql psql (9.5.3, server 9.4.6) Type "help" for help. -- Connect to your database postgres=# \connect yourdatabase; -- A. Check the MAX(id) in your table postgres=# SELECT MAX(id) FROM yourtable; -- B. Check the next value that the database will use when it will create new row SELECT nextval('yourtable_id_seq'); -- The result in Step B should be higher than the result in Step A -- If it is not higher, run the command below to try and fix it. -- Please run a pg_dump just in case :) SELECT setval('yourtable_id_seq', (SELECT MAX(id) FROM yourtable));
If You find that there are too many tables in your project and You are doing this for a Django > v 1.7 project. You can try the steps mentioned below.
- Take a
pg_dumpjust in case :)
python manage.py dumpdata > datadump.json
- Drop your database, example:
DROP DATABASE django;
- Create the database again, example:
CREATE DATABASE django;
python manage.py migrateto create all your tables again
python manage.py loaddata > datadump.json
loaddata command in Django will take care of updating the id sequences for your tables ;)