#30246: Using an annotated field calculated with
django.db.models.functions.Extract
in aggregate results in ProgrammingError
-------------------------------------+-------------------------------------
Reporter: Jan | Owner: nobody
Baryła |
Type: Bug | Status: new
Component: Database | Version: 2.1
layer (models, ORM) | Keywords: query, aggregate,
Severity: Normal | extract, annotate
Triage Stage: | Has patch: 0
Unreviewed |
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-------------------------------------+-------------------------------------
Aggregating most annotated fields works as expected, but if I put a
DateTimeField through Extract during the annotate step, I get a
ProgrammingError when trying to aggregate.
**models.py**
{{{
class MyModel(models.Model):
usage_time = models.DateTimeField()
usage = models.FloatField()
}}}
I would like to take the whole queryset, and calculate hourly usages. I
figured using the django.db.models.functions.Extract transform would suit
my needs well. This is the sample piece of code that, in a perfect
scenario, would give me a dictionary filled with key value pairs, where
key is the hour, and value is the sum of usages measured in that hour.
{{{
hour_aggregates = {}
for i in range(24):
hour_aggregates['{}_{}'.format("am" if i < 12 else "pm", i)] =
Sum("usage", filter=Q(hour=i))
usages = MyModel.objects.annotate(hour=Extract("usage_time",
"hour")).aggregate(**hour_aggregates)
}}}
Unfortunately, I get the following error:
{{{
Traceback (most recent call last):
File "/home/jan/project/env/lib/python3.6/site-
packages/django/db/backends/utils.py", line 85, in _execute
return self.cursor.execute(sql, params)
psycopg2.ProgrammingError: column "__col2" does not exist
LINE 1: ...CT "package_mymodel"."id" AS Col1, EXTRACT('hour' FROM "__col2"
A...
}}}
This occured to me while using Django 2.1.7. It doesn't work on 2.2b1, but
I have tested this solution on Django 1.8 and it works, which is why I am
filing this bug report. My Python version is 3.6.7 and I'm using
PostgreSQL 10.6.
--
Ticket URL: <https://code.djangoproject.com/ticket/30246>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-updates+unsubscr...@googlegroups.com.
To post to this group, send email to django-updates@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/052.56a07b8d18839a429e9a099ec2f0a338%40djangoproject.com.
For more options, visit https://groups.google.com/d/optout.
