Item update query produced by django is wrong

Question

I am trying to update one item at a time using the Django ORM with TimescaleDB as my database.

I have a timesacle hypertable defined by the following model:

class RecordTimeSeries(models.Model):

    # NOTE: We have removed the primary key (unique constraint) manually, since we don't want an id column
    timestamp = models.DateTimeField(primary_key=True)
    location = PointField(srid=settings.SRID, null=True)
    station = models.ForeignKey(Station, on_delete=models.CASCADE)
    # This is a ForeignKey and not an OneToOneField because of [this](https://stackoverflow.com/questions/61205063/error-cannot-create-a-unique-index-without-the-column-date-time-used-in-part)
    record = models.ForeignKey(Record, null=True, on_delete=models.CASCADE)
    temperature_celsius = models.FloatField(null=True)

    class Meta:
        unique_together = (
            "timestamp",
            "station",
            "record",
        )

When I update the item using save():

record_time_series = models.RecordTimeSeries.objects.get(
    record=record,
    timestamp=record.timestamp,
    station=record.station,
)

record_time_series.location=record.location
record_time_series.temperature_celsius=temperature_celsius
record_time_series.save()

I get the following error:

psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "5_69_db_recordtimeseries_timestamp_station_id_rec_0c66b9ab_uniq"
DETAIL:  Key ("timestamp", station_id, record_id)=(2022-05-25 09:15:00+00, 2, 2) already exists.

and I see that the query that django used is the following:

{'sql': 'UPDATE "db_recordtimeseries" SET "location" = NULL, "station_id" = 2, "record_id" = 2, "temperature_celsius" = 26.0 WHERE "db_recordtimeseries"."timestamp" = \'2022-05-25T09:15:00\'::timestamp', 'time': '0.007'}

On the other hand the update is successful with update():

record_time_series = models.RecordTimeSeries.objects.filter(
    record=record,
    timestamp=record.timestamp,
    station=record.station,
)
record_time_series.update(
    location=record.location,
    temperature_celsius=temperature_celsius,
)

and the sql used by django is:

{'sql': 'UPDATE "db_recordtimeseries" SET "location" = NULL, "temperature_celsius" = 25.0 WHERE ("db_recordtimeseries"."record_id" = 2 AND "db_recordtimeseries"."station_id" = 2 AND "db_recordtimeseries"."timestamp" = \'2022-05-25T09:15:00\'::timestamp)', 'time': '0.012'}

Obviously, the first query is wrong because it does not have the correct parameters in the WHERE clause, but why doesn't django include those parameters, since timestamp is not a unique key, and how can this be fixed?

Answer 1

I think the error was caused because of foregn_key:

Firstly:

Be aware that the update() method is converted directly to an SQL statement. It is a bulk operation for direct updates. It doesn’t run any save() methods on your models, or emit the pre_save or post_save signals (which are a consequence of calling save()), or honor the auto_now field option.

source

Secondly:

Analogous to ON DELETE there is also ON UPDATE which is invoked when a referenced column is changed (updated). The possible actions are the same. In this case, CASCADE means that the updated values of the referenced column(s) should be copied into the referencing row(s).

source