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

Question

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!!

I am not sure what caused the problem but have you tried `Medicine.objects.filter(unique_item_id=26775).first()`? — Carlos, Oct 10 '20 at 11:51
This is a bit weird, Is this happens only with PostgreSQL? If so, is it reproducible with a ***fresh project***? @RohitChopra — JPG, Oct 18 '20 at 13:25
I am not sure if it will occur in fresh project. My project is 3 years old and data keeps on changing — Rohit Chopra, Oct 18 '20 at 13:31

score 6 · Accepted Answer · answered Oct 19 '20 at 15:06

6

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

answered Oct 19 '20 at 15:06

Simon Charette

5,009
1
25
33

1

Indeed and I would suggest to check what is going on directly in the DB using psql in console or PgAdmin. select * from medicine where unique_item_id = 26775. – openHBP Oct 21 '20 at 09:50
"... any kind of filtering ... then you're likely to hit this issue ..." Could you explain why? What is the underlying cause? I know it's there in your example, but a short explanation would be of great help. :-) – djvg Oct 13 '22 at 12:04
The base manager filtering pitfall is also mentioned in the docs: [don-t-filter-away-any-results-in-this-type-of-manager-subclass](https://docs.djangoproject.com/en/4.1/topics/db/managers/#don-t-filter-away-any-results-in-this-type-of-manager-subclass) – djvg Oct 13 '22 at 12:20

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

1 Answers1