Getting django.db.utils.IntegrityError: duplicate key value violates unique constraint But value does not exists

user_5533995 0
Viewed 1802

I am using PostgreSQL with Django and I was trying to get some object from DB using Django ORM.

Medicine.objects.get(unique_item_id=26775)

But while fetching I was caught up with Error -> item_medicine.models.DoesNotExist: Medicine matching query does not exist.

Then I tried Inserting the same using Django ORM.

Medicine.objects.create(unique_item_id=26775)

But again I am getting error psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "item_medicine_medicine_unique_item_id_key" DETAIL: Key (unique_item_id)=(26775) already exists.

In my models I have added unique=True for unique_item_id field.

I don't know why is this happening. I tried answers given on similar posts But nothing worked.

Traceback:

Traceback (most recent call last):
  File "/home/rohit/virtualenvs/envmedicine/lib/python3.6/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "item_medicine_medicine_unique_item_id_key"
DETAIL:  Key (unique_item_id)=(26775) already exists.
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
  File "/home/rohit/virtualenvs/envmedicine/lib/python3.6/site-packages/IPython/core/interactiveshell.py", line 2963, in run_code
    exec(code_obj, self.user_global_ns, self.user_ns)
  File "<ipython-input-4-46fdec6a582b>", line 1, in <module>
    Medicine.objects.create(unique_item_id=26775)
  File "/home/rohit/virtualenvs/envmedicine/lib/python3.6/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/rohit/virtualenvs/envmedicine/lib/python3.6/site-packages/django/db/models/query.py", line 394, in create
    obj.save(force_insert=True, using=self.db)
  File "/home/rohit/Projects/medicine/item_medicine/models.py", line 58, in save
    super(Medicine, self).save(*args, **kwargs)
  File "/home/rohit/virtualenvs/envmedicine/lib/python3.6/site-packages/django/db/models/base.py", line 808, in save
    force_update=force_update, update_fields=update_fields)
  File "/home/rohit/virtualenvs/envmedicine/lib/python3.6/site-packages/django/db/models/base.py", line 838, in save_base
    updated = self._save_table(raw, cls, force_insert, force_update, using, update_fields)
  File "/home/rohit/virtualenvs/envmedicine/lib/python3.6/site-packages/django/db/models/base.py", line 924, in _save_table
    result = self._do_insert(cls._base_manager, using, fields, update_pk, raw)
  File "/home/rohit/virtualenvs/envmedicine/lib/python3.6/site-packages/django/db/models/base.py", line 963, in _do_insert
    using=using, raw=raw)
  File "/home/rohit/virtualenvs/envmedicine/lib/python3.6/site-packages/django/db/models/manager.py", line 85, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
  File "/home/rohit/virtualenvs/envmedicine/lib/python3.6/site-packages/django/db/models/query.py", line 1076, in _insert
    return query.get_compiler(using=using).execute_sql(return_id)
  File "/home/rohit/virtualenvs/envmedicine/lib/python3.6/site-packages/django/db/models/sql/compiler.py", line 1112, in execute_sql
    cursor.execute(sql, params)
  File "/home/rohit/virtualenvs/envmedicine/lib/python3.6/site-packages/django/db/backends/utils.py", line 79, in execute
    return super(CursorDebugWrapper, self).execute(sql, params)
  File "/home/rohit/virtualenvs/envmedicine/lib/python3.6/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
  File "/home/rohit/virtualenvs/envmedicine/lib/python3.6/site-packages/django/db/utils.py", line 94, in __exit__
    six.reraise(dj_exc_type, dj_exc_value, traceback)
  File "/home/rohit/virtualenvs/envmedicine/lib/python3.6/site-packages/django/utils/six.py", line 685, in reraise
    raise value.with_traceback(tb)
  File "/home/rohit/virtualenvs/envmedicine/lib/python3.6/site-packages/django/db/backends/utils.py", line 64, in execute
    return self.cursor.execute(sql, params)
django.db.utils.IntegrityError: duplicate key value violates unique constraint "item_medicine_medicine_unique_item_id_key"
DETAIL:  Key (unique_item_id)=(26775) already exists.

Thank you guys in Advance!!

unique-constraint postgresql django-orm django-models django
1 Answers

If you're doing any kind of filtering in your Medicine.objects manager then you're likely to hit this issue. For example, assuming you have the following model and manager defined.

class SoftDeleteManager(models.Manager):
    def get_queryset(self):
        return super().get_queryset().filter(deleted_at=None)

class Medecine(models.Model):
    ...
    unique_item_id = models.IntegerField(unique=True)
    deleted_at = models.DateTimeField(null=True)

    objects = SoftDeleteManager()

conflicting = Medecine.objects.create(unique_item_id=26775, deleted_at=some_datetime)

Medecine.objects.get(unique_item_id=26775)  # Results in DoesNotExist
Medecine.objects.create(unique_item_id=26775) # Results in IntegrityError

If it's not the case then you might have a corrupted PostgreSQL index.

I'd suggest you try running the REINDEX command on your table

REINDEX TABLE item_medicine_medicine

user_701910
Related