update_or_create() doesn't work properly when model has auto_now=True

[Andrew Chiw]

I'm writing an exchange rate app:

models.py

from django.db import models


class ExchangeRate(models.Model):
    base = models.CharField(max_length=3)
    target = models.CharField(max_length=3)
    rate = models.DecimalField(max_digits=17, decimal_places=8)
    date = models.DateField(auto_now=True)

    def __str__(self):
        return "{}->{} on {}".format(self.base, self.target, str(self.date))

    class Meta:
        unique_together = (("base", "target", "date"),)

fx.py

from datetime import datetime, date
import requests

from .models import ExchangeRate


def get_eur_rates():
    url = 'http://api.fixer.io/latest'
    eur_rates = requests.get(url).json()
    print(eur_rates)
    for target in eur_rates["rates"]:
        import ipdb; ipdb.set_trace()
        ExchangeRate.objects.update_or_create(base="EUR", target=target, date=eur_rates["date"], defaults={"rate": eur_rates["rates"][target]})
    print(ExchangeRate.objects.all())

I already have ExchangeRate objects in the database from 2017-01-29.
Now, when I want to run update_or_create() with date='2017-01-27', it fails:

ipdb> ExchangeRate.objects.update_or_create(base="EUR", target=target, date='2017-01-27', defaults={"rate": eur_rates["
rates"][target]})
*** django.db.utils.IntegrityError: duplicate key value violates unique constraint "fx_exchangerate_base_f6916782_uniq"
DETAIL:  Key (base, target, date)=(EUR, CZK, 2017-01-29) already exists.

I'm sure Django is working internally in a way that might result in this error, but this shouldn't happen and the error message is confusing.

[Tim Graham]

This looks like invalid usage of auto_now. As ​the documentation says, "Automatically set the field to now every time the object is saved. Useful for “last-modified” timestamps. Note that the current date is always used; it’s not just a default value that you can override." It seems like update_or_create() can't work intuitively with an auto_now field as whatever value you specify is ignored.

I'm not sure if anything should be done to improve the situation considering that there are suggestions to deprecate auto_now; see #22995 for details and alternatives.

[Ryan Castner]

The fact that you use unique_together = (("base", "target", "date"),) is causing the error you are seeing, it is trying to put two values in there since auto_now adds the current datetime as Tim stated as well as your provided datetime value and it can't create two primary keys as it violates the single unique primary key constraint generated by those three fields.