Opened 9 years ago
Last modified 9 months ago
#25591 new New feature
Cannot QuerySet.update DateRangeField using F() expressions
Reported by: | synotna | Owned by: | nobody |
---|---|---|---|
Component: | contrib.postgres | Version: | 1.8 |
Severity: | Normal | Keywords: | |
Cc: | Claude Paroz | Triage Stage: | Accepted |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
It is not currently possible to QuerySet.update a DateRangeField using F() expressions - should it be?
class MyModel(models.Model): date_from = models.DateField() date_to = models.DateField() period = DateRangeField(null=True)
MyModel.objects.update(period=(F('date_from'), F('date_to'))) Traceback (most recent call last): File "<input>", line 1, in <module> File "/home/tech/.pyenv/versions/partner-backend-2015-08-11/lib/python3.4/site-packages/django/db/models/manager.py", line 127, in manager_method return getattr(self.get_queryset(), name)(*args, **kwargs) File "/home/tech/.pyenv/versions/partner-backend-2015-08-11/lib/python3.4/site-packages/django/db/models/query.py", line 563, in update rows = query.get_compiler(self.db).execute_sql(CURSOR) File "/home/tech/.pyenv/versions/partner-backend-2015-08-11/lib/python3.4/site-packages/django/db/models/sql/compiler.py", line 1062, in execute_sql cursor = super(SQLUpdateCompiler, self).execute_sql(result_type) File "/home/tech/.pyenv/versions/partner-backend-2015-08-11/lib/python3.4/site-packages/django/db/models/sql/compiler.py", line 840, in execute_sql cursor.execute(sql, params) File "/home/tech/.pyenv/versions/partner-backend-2015-08-11/lib/python3.4/site-packages/django/db/backends/utils.py", line 79, in execute return super(CursorDebugWrapper, self).execute(sql, params) File "/home/tech/.pyenv/versions/partner-backend-2015-08-11/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute return self.cursor.execute(sql, params) File "/home/tech/.pyenv/versions/partner-backend-2015-08-11/lib/python3.4/site-packages/django/db/utils.py", line 97, in __exit__ six.reraise(dj_exc_type, dj_exc_value, traceback) File "/home/tech/.pyenv/versions/partner-backend-2015-08-11/lib/python3.4/site-packages/django/utils/six.py", line 658, in reraise raise value.with_traceback(tb) File "/home/tech/.pyenv/versions/partner-backend-2015-08-11/lib/python3.4/site-packages/django/db/backends/utils.py", line 64, in execute return self.cursor.execute(sql, params) File "/home/tech/.pyenv/versions/partner-backend-2015-08-11/lib/python3.4/site-packages/psycopg2/_range.py", line 235, in getquoted a = adapt(r.lower) django.db.utils.ProgrammingError: can't adapt type 'F'
Currently (F('date_from'), F('date_to')) is turned into psycopg2's DateRange(F(date_from), F(date_to), '[)'), which obviously will not work as it cannot handle F() expressions
I imagine Django needs its own DateRange that can handle them?
Change History (8)
comment:1 by , 9 years ago
comment:2 by , 9 years ago
comment:3 by , 9 years ago
I do think that F()
objects make sense here, but they're going to have to go through a custom TypeRange() expression. We could and should handle this internally though. Users shouldn't need to know whether or not they need to import XRange
from psycopg
or from contrib.postgres
based on whether or not they want to support F().
I'm not intimately familiar with the contrib.postgres module, so I'm not sure if the psycopg2.extras.XRange types are usually imported by users or not.
RangeField
already has a get_prep_value
which then wraps the values in the underlying psycopg
types. It could inspect the content of value
and then wrap inside a custom range_type_expression rather than range_type.
This is just throwing ideas at a wall though. I'd be interested in what Marc Tamlyn has to say.
Also, fwiw, your DateRange
Func above does not need to define template
as that's exactly the default anyway.
comment:4 by , 9 years ago
Component: | Uncategorized → contrib.postgres |
---|---|
Triage Stage: | Unreviewed → Accepted |
Type: | Uncategorized → New feature |
comment:5 by , 6 years ago
Using Python 3.5, Django 1.11, PostgreSQL 9.6. I'm not sure if I'm missing something really simple, or this really isn't as straight-forward as one would expect.
I want to migrate from 2 individual FloatField
s to a single FloatRangeField
; with a model like this:
class MyModel(models.Model): age_min = models.FloatField(null=True, blank=True) age_max = models.FloatField(null=True, blank=True) age_range = FloatRangeField(null=True, blank=True)
I'm unable to figure out how to use .update()
with a custom Func
:
class AgeRange(Func): function = 'numrange'
From what I've read in this ticket, that Func
ought to allow the following to work:
MyModel.objects.all().update(age_range=AgeRange('age_min', 'age_max'))
However, I end up with the following traceback:
... File "/home/ubuntu/.virtualenvs/myproject/lib/python3.5/site-packages/django/db/backends/utils.py", line 64, in execute return self.cursor.execute(sql, params) django.db.utils.ProgrammingError: function numrange(double precision, double precision) does not exist LINE 1: UPDATE "myapp_mymodel" SET "age_range" = numrange("act... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.
The pertinent fields in PostgreSQL:
Column | Type | Modifiers -------------------------+--------------------------+---------------------- age_min | double precision | age_max | double precision | age_range | numrange |
Assuming that the PostgreSQL output You might need to add explicit type casts
is the real cause, how would I adapt my Func
to address this? Based on https://www.postgresql.org/docs/9.6/static/rangetypes.html#RANGETYPES-BUILTIN, numrange
handles postgres numeric
types, of which double precision
is one according to https://www.postgresql.org/docs/9.6/static/datatype-numeric.html.
follow-up: 7 comment:6 by , 6 years ago
It looks like FloatRangeField
should actually have been called DecimalRangeField
because FloatField
s are stored as double precision
aka float
and DecimalField
are stored as numeric
. FloatRangeField
is stored as numrange
which only accepts numeric
values.
The crash you are getting here is because numrange(float, float)
simply doesn't exist. If you want to get it working I suggest you either switch to using DecimalField
s or use Cast
s.
update(age_range=AgeRange(Cast('age_min', DecimalField()), Cast('age_max', DecimalField())))
comment:7 by , 2 years ago
Since the incorrectly named FloatRangeField
has long since been updated to DecimalRangeField
(https://github.com/django/django/commit/6de7f9ec60fbdc59797bc21803f16260bd203f04), this ticket can probably be closed out.
comment:8 by , 9 months ago
Cc: | added |
---|
A solution to support the assignment of tuple[Expr, Expr]
here is likely to have RangeField
special case the assignment of such objects in its get_prep_value
method by wrapping in in a Func
as suggested in comment:1 instead of systematically creating a range_type(*value)
. This will cover the following case
obj.period = (F('date_from'), F('date_to')) obj.save()
In order to support the QuerySet.update
case I don't see a way of doing it without adapting UpdateQuery
to special case tuple | list
that contain expressions. Possibly by having it call field.get_db_prep_value
which defaults to get_prep_value
and would engage the changes described in the first half of comment.
For what it's worth, a workaround is creating a daterange Func expression, i.e.