How to Fix Django / PSQL "duplicate key value violates unique constraint" error

I just recently started using Django at my business and I absolutely adore it. It's so easy to just customize every single element as well as get things done extremely fast. Outside of the easy to learn syntax and Model/View system is the easy database migration system. An easy ./manage.py migrate will do all of the necessary code to do all the complex logic in your database. The negative side to this is that sometimes it just doesn't 100% do what it needs to do. In this instance, I had a lot of POST requests not working because I would be violating unique constraints in my database.

What does this error even mean!?

The error is telling you that you are trying to create an object that already has a primary key. For example, if I created a Book object with a primary key of 1 and there is already another Book object with the same primary key, you will get this error as it means it would have to delete this object in order for it to be unique.

Why did I get this error if I already have data there!?

When you changed your database over, chances are you moved your data without any other configuration. In this instance, the data you created on your local machine has stayed on there but your PostgreSQL setup on the production side is still configured as it was just created. PostgreSQL uses something called sequences to determine AUTO-INCREMENTING values such as a primary key.

TL;DR We need to reset the sequences to be one above the primary key that is there.

How can I fix this!?

I first ran into this bug when working with Django CMS. My problem was that I wasn't able to upload any pictures. Since there are lots of different applications under my INSTALLED_APPS, I didn't know what table in my database was being effected. If you already know what table/model to fix then feel free to skip this step. I will be on Ubuntu for this tutorial.

Navigate to:

$ cd /var/log/postgresql/the_postgres_logfile

For me the error log contained this:

duplicate key value violates unique constraint "easy_thumbnails_thumbnail_pkey"

Nice, the issue is in the easy_thumbnails application. Now that I have this knowledge, I am going to then open a tab where I can log into my PostgreSQL database. The command for this is:

$ psql -U my_user my_database

Enter your password and you will be in your psql shell.

Open up another tab and navigate towards your project directory. Run this command:

$ python manage.py sqlsequencereset <application>

Replace <application> with the app that is giving you problems. Now, this won't fix your problem. Instead, it gives you the code to fix your problem. The code given to you will look somethign like this:

BEGIN;

SELECT setval(pg_get_serial_sequence('"abcd"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "easy_thumbnails_source";

SELECT setval(pg_get_serial_sequence('"abcd"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "easy_thumbnails_thumbnail";

SELECT setval(pg_get_serial_sequence('"abcd"','id'), coalesce(max("id"), 1), max("id") IS NOT null) FROM "easy_thumbnails_thumbnaildimensions";

COMMIT;

Make sure you have a copy of this.

Go back to your PSQL shell and then insert every SELECT statement and execute them. This will then reset the sequence to be at the latest primary key.

Comments

comments powered by Disqus