Opened 9 years ago

Last modified 10 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 synotna, 9 years ago

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

comment:2 by Tim Graham, 9 years ago

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.

comment:3 by Josh Smeaton, 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 Tim Graham, 9 years ago

Component: Uncategorizedcontrib.postgres
Triage Stage: UnreviewedAccepted
Type: UncategorizedNew feature

comment:5 by James Addison, 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 FloatFields 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.

Last edited 6 years ago by James Addison (previous) (diff)

comment:6 by Simon Charette, 6 years ago

It looks like FloatRangeField should actually have been called DecimalRangeField because FloatFields 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 DecimalFields or use Casts.

update(age_range=AgeRange(Cast('age_min', DecimalField()), Cast('age_max', DecimalField())))

in reply to:  6 comment:7 by Jack Linke, 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 Simon Charette, 10 months ago

Cc: Claude Paroz 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.

Note: See TracTickets for help on using tickets.
Back to Top