3

I have the next queryset:

Article.objects.filter(finished=True, updated_at__range=[start, end]).extra(
    select={'hour': 'extract(hour from updated_at)'}).values('hour').annotate(categorized=Count('id'))

And I get the error :

"ProgrammingError: column reference "updated_at" is ambiguous LINE 1: SELECT (extract(hour from updated_at)) AS "hour", COUNT("art..."

How should I go about this?

Edit: The queryset works without filtering by date "updated_at__range=[start, end]" but I need that filter.

aircraft
  • 25,146
  • 28
  • 91
  • 166
Andrés Root
  • 642
  • 1
  • 7
  • 11

2 Answers2

8

This is nothing to do with Django. You're inserting raw SQL (the extract clause) but failing to qualify the table that updated_at is coming from within it. Assuming the Article model is in an app called "myapp" it would be something like this:

select={'hour': 'extract(hour from myapp_article.updated_at)'})
Daniel Roseman
  • 588,541
  • 66
  • 880
  • 895
7

If you'd come here through a web search: I started having the error column reference "created" is ambiguous when I added prefetch_related and select_related statements to my filter.

While originally my query .extra(where=['EXTRACT(hour from created) >19 OR EXTRACT(hour from created) <6']) worked, it stopped working when select_related/prefetch) was added.

The solution was to add myappname_mymodelname. before the field name.

SaeX
  • 17,240
  • 16
  • 77
  • 97
  • Just 2 cents: the "mymodelname" for something like "class CaseModel" would be "casemodel" – Shadi Nov 30 '17 at 13:13