Will & Skill Developers

Will & Skill Developers

Thoughts, snippets and ideas from the team at Will & Skill AB, Stockholm.

Faisal Mahmud

“The mind is not a vessel to be filled, but a fire to be kindled.” ― Plutarch


Handling Postgres auto-increment field errors when migrating from legacy databases

Faisal MahmudFaisal Mahmud

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));  

Source: http://www.henning.ms/2012/08/23/fixing-auto-increment-sequences-in-postgresql/

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.

  1. Take a pg_dump just in case :)
  2. Run python manage.py dumpdata > datadump.json
  3. Drop your database, example: DROP DATABASE django;
  4. Create the database again, example: CREATE DATABASE django;
  5. Run python manage.py migrate to create all your tables again
  6. Run python manage.py loaddata > datadump.json

The loaddata command in Django will take care of updating the id sequences for your tables ;)

Faisal Mahmud

Faisal Mahmud

“The mind is not a vessel to be filled, but a fire to be kindled.” ― Plutarch