[Django] #25591: Cannot QuerySet.update DateRangeField using F() expressions
Django
-------------------------------+--------------------
Reporter: synotna | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 1.8
Severity: Normal | Keywords:
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
-------------------------------+--------------------
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?
--
Ticket URL: <https://code.djangoproject.com/ticket/25591>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: synotna | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 1.8
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by synotna):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Comment:
For what it's worth, a workaround is creating a daterange Func expression,
i.e.
{{{
class DateRange(Func):
function = 'daterange'
template = '%(function)s(%(expressions)s)'
}}}
{{{
>>> MyModel.objects.update(period=DateRange('date_from', 'date_to'))
101
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25591#comment:1>
Django
Reporter: synotna | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 1.8
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by timgraham):
Someone with a deeper understanding of expressions can probably offer an
opinion about whether or not we should try to make `F()` "jack of all
trades" or if we should promote using more specific functions like you
mentioned. Some related tickets about query date ranges: #22288, #16487.
--
Ticket URL: <https://code.djangoproject.com/ticket/25591#comment:2>
Django
Reporter: synotna | Owner: nobody
Type: Uncategorized | Status: new
Component: Uncategorized | Version: 1.8
Keywords: | Triage Stage: Unreviewed
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by jarshwah):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/25591#comment:3>
Django
Reporter: synotna | Owner: nobody
Type: New feature | Status: new
Component: contrib.postgres | Version: 1.8
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by timgraham):
* type: Uncategorized => New feature
* component: Uncategorized => contrib.postgres
* stage: Unreviewed => Accepted
--
Ticket URL: <https://code.djangoproject.com/ticket/25591#comment:4>
Django
Reporter: synotna | Owner: nobody
Type: New feature | Status: new
Component: contrib.postgres | Version: 1.8
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by James Addison):
Using Python 3.5, Django 1.11. 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].
--
Ticket URL: <https://code.djangoproject.com/ticket/25591#comment:5>
Django
Reporter: synotna | Owner: nobody
Type: New feature | Status: new
Component: contrib.postgres | Version: 1.8
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Simon Charette):
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.
{{{#!python
update(age_range=AgeRange(Cast('age_min', DecimalField()), Cast('age_max',
DecimalField())))
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25591#comment:6>
Django
Reporter: synotna | Owner: nobody
Type: New feature | Status: new
Component: contrib.postgres | Version: 1.8
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Jack Linke):
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/25591#comment:7>
Django
Reporter: synotna | Owner: nobody
Type: New feature | Status: new
Component: contrib.postgres | Version: 1.8
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Simon Charette):
* cc: Claude Paroz (added)
Comment:
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`
[https://github.com/django/django/blob/1592f0ac220c1fd37779f6d33efb28ebd60e2e66/django/contrib/postgres/fields/ranges.py#L94-L95
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
{{{#!python
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
[https://github.com/django/django/blob/6debeac9e7538e0e32883dc36abe6fc40a35c874/django/db/models/sql/subqueries.py#L104-L117
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/25591#comment:8>