django.db.utils.IntegrityError: duplicate key value violates unique constraint "spirit_category_category_pkey"

Question

The Error

I'm using django and spirit to build a website. In a test, when I was inserting new data into a table called spirit_category_category, I got the following error:

django.db.utils.IntegrityError: duplicate key value violates unique constraint "spirit_category_category_pkey"
DETAIL:  Key (id)=(1) already exists.

Note that there were already two other records in the table with ids of 1 and 2. So inserting Key(id)=(1) wouldn't work, of course. But the sql executed didn't include id field. That is, Key (id)=(1) was automatically generated by postgresql, why did it generate an already existed id?

The Reason

To find out the reason, I ran the following command in postgresql:

test_spiritdb=# select start_value, last_value, max_value from spirit_category_category_id_seq;
 start_value | last_value |      max_value      
-------------+------------+---------------------
           1 |          1 | 9223372036854775807
(1 row)

So basically, last_value is 1, so postgresql will generate Key (id)=(1) every time, I tried to alter it to 3, and everything is fine.

test_spiritdb=# alter sequence spirit_category_category_id_seq restart with 3;

I don't know how to fix it for a test

The test passed. But it is a test, so it is meaningless to alter a test table because the test database will be deleted and created again for every test, so the next time the test will fail again because last_value will still be generated as 1. So I wondered why django/postgresql would generate such an abnormal value for last_value? How to fix it? The models and migrations for category are as follows, if it helps.

models.py

# -*- coding: utf-8 -*-

from __future__ import unicode_literals

from django.db import models
from django.utils.translation import ugettext_lazy as _
from django.core.urlresolvers import reverse
from django.conf import settings

from .managers import CategoryQuerySet
from ..core.utils.models import AutoSlugField

class Category(models.Model):

    parent = models.ForeignKey('self', verbose_name=_("category parent"), null=True, blank=True)

    title = models.CharField(_("title"), max_length=75)
    slug = AutoSlugField(populate_from="title", db_index=False, blank=True)
    description = models.CharField(_("description"), max_length=255, blank=True)
    is_global = models.BooleanField(_("global"), default=True,
                                    help_text=_('Designates whether the topics will be'
                                                'displayed in the all-categories list.'))
    is_closed = models.BooleanField(_("closed"), default=False)
    is_removed = models.BooleanField(_("removed"), default=False)
    is_private = models.BooleanField(_("private"), default=False)

    # topic_count = models.PositiveIntegerField(_("topic count"), default=0)

    objects = CategoryQuerySet.as_manager()

    class Meta:
        ordering = ['title', 'pk']
        verbose_name = _("category")
        verbose_name_plural = _("categories")

    def get_absolute_url(self):
        if self.pk == settings.ST_TOPIC_PRIVATE_CATEGORY_PK:
            return reverse('spirit:topic:private:index')
        else:
            return reverse('spirit:category:detail', kwargs={'pk': str(self.id), 'slug': self.slug})

    @property
    def is_subcategory(self):
        if self.parent_id:
            return True
        else:
            return False

0001_initial.py

# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import models, migrations
import spirit.core.utils.models


class Migration(migrations.Migration):

    dependencies = [
    ]

    operations = [
        migrations.CreateModel(
            name='Category',
            fields=[
                ('id', models.AutoField(primary_key=True, verbose_name='ID', serialize=True, auto_created=True)),
                ('title', models.CharField(verbose_name='title', max_length=75)),
                ('slug', spirit.core.utils.models.AutoSlugField(db_index=False, populate_from='title', blank=True)),
                ('description', models.CharField(verbose_name='description', max_length=255, blank=True)),
                ('is_closed', models.BooleanField(verbose_name='closed', default=False)),
                ('is_removed', models.BooleanField(verbose_name='removed', default=False)),
                ('is_private', models.BooleanField(verbose_name='private', default=False)),
                ('parent', models.ForeignKey(null=True, verbose_name='category parent', to='spirit_category.Category', blank=True)),
            ],
            options={
                'ordering': ['title', 'pk'],
                'verbose_name': 'category',
                'verbose_name_plural': 'categories',
            },
        ),
    ]

0002_auto_20150728_0442.py

# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import models, migrations
from django.conf import settings


def default_categories(apps, schema_editor):
    Category = apps.get_model("spirit_category", "Category")

    if not Category.objects.filter(pk=settings.ST_TOPIC_PRIVATE_CATEGORY_PK).exists():
        Category.objects.create(
            pk=settings.ST_TOPIC_PRIVATE_CATEGORY_PK,
            title="Private",
            slug="private",
            is_private=True
        )

    if not Category.objects.filter(pk=settings.ST_UNCATEGORIZED_CATEGORY_PK).exists():
        Category.objects.create(
            pk=settings.ST_UNCATEGORIZED_CATEGORY_PK,
            title="Uncategorized",
            slug="uncategorized"
        )


class Migration(migrations.Migration):

    dependencies = [
        ('spirit_category', '0001_initial'),
    ]

    operations = [
        migrations.RunPython(default_categories),
    ]

0003_category_is_global.py

# -*- coding: utf-8 -*-
from __future__ import unicode_literals

from django.db import models, migrations


class Migration(migrations.Migration):

    dependencies = [
        ('spirit_category', '0002_auto_20150728_0442'),
    ]

    operations = [
        migrations.AddField(
            model_name='category',
            name='is_global',
            field=models.BooleanField(default=True, help_text='Designates whether the topics will bedisplayed in the all-categories list.', verbose_name='global'),
        ),
    ]

Searene · Accepted Answer · 2016-01-10 03:35:16Z

After lots of debugging, I finally found the solution. The reason is that I was trying to insert two another categories with specified ids, which would cause postgresql stop increasing the last_value of the relative sequence. Just as follows:

0002_auto_20150728_0442.py

if not Category.objects.filter(pk=settings.ST_TOPIC_PRIVATE_CATEGORY_PK).exists():
    Category.objects.create(
        pk=settings.ST_TOPIC_PRIVATE_CATEGORY_PK,
        title="Private",
        slug="private",
        is_private=True
    )

if not Category.objects.filter(pk=settings.ST_UNCATEGORIZED_CATEGORY_PK).exists():
    Category.objects.create(
        pk=settings.ST_UNCATEGORIZED_CATEGORY_PK,
        title="Uncategorized",
        slug="uncategorized"
    )

The way to fix this is simple, either change the last_value manually in django, or just don't specify the id, i.e. remove the following lines:

....
pk=settings.ST_TOPIC_PRIVATE_CATEGORY_PK,
....
pk=settings.ST_UNCATEGORIZED_CATEGORY_PK,
....

I guess if you let django undertake the task of managing id, it may not be a good idea to specify the id yourself when inserting new data.

YPCrumble · Accepted Answer · 2016-01-09 22:56:26Z

0

I don't think the issue is with your migrations.

You're trying to add multiple identical spirit_category_category objects which will trigger the same auto-incremented id if not configured correctly in the Django test suite. One option would be to identify the conflicting tests and move them into separate TestCase classes (because setUp will flush your database for you).

Another option would be to use a library like Factory Boy to create your model instances which will help avoid conflicts like this.

answered Jan 9, 2016 at 22:56

YPCrumble

29.1k26 gold badges115 silver badges177 bronze badges

1 Comment

Searene Over a year ago

I was inserting the same id into the table, just as you say. I removed the id part and everything was fine again. Thank you!

vmenezes · Accepted Answer · 2016-12-12 19:01:52Z

I had the same issue but I needed to keep the ID as I am restoring data from another server and have keep the relationships, etc. My solution was to add another command on the migrations file to run after inserting the items and reset the database sequence for the involved tables.

To get the command to reset the table sequence you could run python manage.py sqlsequencereset spirit as documented on https://docs.djangoproject.com/en/1.9/ref/django-admin/#sqlsequencereset

Then on your migration 0002_auto_20150728_0442.py file add:

from django.db connection

def reset_spirit_pk_sequence(apps, schema_editor):
    with connection.cursor() as cursor:
        cursor.execute("RESULT_FROM_SQLRESETSEQUENCE")
    row = cursor.fetchone()

...
...

operations = [
    migrations.RunPython(default_categories),
    migrations.RunPython(reset_spirit_pk_sequence),
]

Note to replace RESULT_FROM_SQLRESETSEQUENCE with the line of command you got from the manage.py sqlresetsequence related to the table you are having problems(escaping inner " with \).

Rick Graves · Accepted Answer · 2019-12-18 23:47:59Z

0

In a test, my code was trying to save a row without passing id (primary key), yet I was getting:

django.db.utils.IntegrityError: duplicate key value violates unique constraint ...
DETAIL:  Key (id)=(1) already exists.

I worked around the problem as follows:

iOneMore = Model.objects.last().id + 1
oNew = Model( id = iOneMore, col1 = string1, col2 = string2 )
oNew.save()

Problem solved, no more error.

answered Dec 18, 2019 at 23:47

Rick Graves

6377 silver badges12 bronze badges

Collectives™ on Stack Overflow

django.db.utils.IntegrityError: duplicate key value violates unique constraint "spirit_category_category_pkey"

The Error

The Reason

I don't know how to fix it for a test

models.py

0001_initial.py

0002_auto_20150728_0442.py

0003_category_is_global.py

4 Answers 4

0002_auto_20150728_0442.py

Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

The Error

The Reason

I don't know how to fix it for a test

models.py

0001_initial.py

0002_auto_20150728_0442.py

0003_category_is_global.py

4 Answers 4

0002_auto_20150728_0442.py

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related