get_or_create failure with Django and Postgres (duplicate key value violates unique constraint)

Question

Thanks for taking time to read my question.

I have a django app with the following model:

class UserProfile(models.Model):
    user = models.OneToOneField(User)
    ...

class Visit(models.Model):
    profile = models.ForeignKey(UserProfile)
    date = models.DateField(auto_now_add=True, db_index=True)
    ip = models.IPAddressField()
    class Meta:
        unique_together = ('profile', 'date', 'ip')

In a view:

profile = get_object_or_404(Profile, pk = ...)
get, create = Visit.objects.get_or_create(profile=profile, date=now.date(), ip=request.META['REMOTE_ADDR'])
if create: DO SOMETHING

Everything works fine, except that the Postgres Logs are full with duplicate key errors:

2012-02-15 14:13:44 CET ERROR:  duplicate key value violates unique constraint "table_visit_profile_id_key"
2012-02-15 14:13:44 CET STATEMENT:  INSERT INTO "table_visit" ("profile_id", "date", "ip") VALUES (1111, E'2012-02-15', E'xx.xx.xxx.xxx') RETURNING "table_visit"."id"

Tried different solution e.g.

from django.db import transaction 
from django.db import IntegrityError

@transaction.commit_on_success
def my_get_or_create(prof, ip):    
    try:
        object = Visit.objects.create(profile=prof, date=datetime.now().date(), ip=ip)
    except IntegrityError:
        transaction.commit()
        object = Visit.objects.get(profile=prof, date=datetime.now().date(), ip=ip)
    return object

....

created = my_get_or_create(prof, request.META['REMOTE_ADDR'])
if created: DO SOMETHING

This only helps for MySQL? Does anyone know how to avaid the duplicate key value errors for postgres?

stackoverflow.com/q/244243/781695

user
– user

2014-05-23 17:04:18 +00:00
Commented May 23, 2014 at 17:04 — user
– user, Commented May 23, 2014 at 17:04

kravietz · Accepted Answer · 2016-04-20 17:15:01Z

13

Another possible reason for these errors in get_or_create() is data type mismatch in one of the search fields - for example passing False instead of None into a nullable field. The .get() inside .get_or_create() will not find it and Django will continue with new row creation - which will fail due to PostgreSQL constraints.

answered Apr 20, 2016 at 17:15

kravietz

11.4k2 gold badges40 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Barmi Over a year ago

Thanks for your answer :)

alphazwest Over a year ago

Ended what would have otherwise been a morning-long headache for me.

Tarek Kalaji Over a year ago

I agree with you on this point, when you set query as: obj, created = UserPermission.objects.get_or_create( user=user, boolean_field=boolean_field) The boolean field is False, it will always try to create object and raise: duplicate key value violates unique constraint because you have defined boolean_field=models.BooleanField(default=False)

MagicLAMP · Accepted Answer · 2024-04-01 11:21:48Z

5

I had issues with get_or_create when using postgres. In the end I abandoned the boilerplate code for traditional:

try:
    job_invite  = Invite.objects.get(sender=employer.user, job=job)
except Invite.DoesNotExist:
    job_invite  = Invite(sender=employer.user, job=job)
    job_invite.save()

edited Apr 1, 2024 at 11:21

answered Dec 10, 2014 at 5:45

MagicLAMP

1,09213 silver badges30 bronze badges

1 Comment

Simon Steinberger Over a year ago

Although this solution will appear to be working on most sites, it will fail on heavy traffic sites where lots of concurrent requests are at work.

Gwilym Humphreys · Accepted Answer · 2012-02-15 16:55:44Z

2

Have you at some point had unique=True set on Visit's profile field?

It looks like there's been a unique constraint generated for postgres that's still in effect. "table_visit_profile_id_key" is what it's auto generated name would be, and naturally it would cause those errors if you're recording multiple visits for a user.

If this is the case, are you using South to manage your database changes? If you aren't, grab it!

answered Feb 15, 2012 at 16:55

Gwilym Humphreys

2311 silver badge3 bronze badges

Comments

kravietz · Accepted Answer · 2013-11-21 12:30:15Z

0

PostgreSQL behaves somewhat differently in some subtle queries, which results in IntegrityError errors, especially after you switch to Django 1.6. Here's the solution - you need to add select_on_save option to each failing model:

class MyModel(models.Model):
     ...
     class Meta:
         select_on_save = True

It's documented here: Options.select_on_save

answered Nov 21, 2013 at 12:30

kravietz

11.4k2 gold badges40 silver badges30 bronze badges

1 Comment

nu everest Over a year ago

What if I am using Django 1.4, and I'm getting integrity errors?

Collectives™ on Stack Overflow

get_or_create failure with Django and Postgres (duplicate key value violates unique constraint)

4 Answers 4

3 Comments

1 Comment

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related