Cannot QuerySet.update DateRangeField using F() expressions

[synotna]

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?

[synotna]

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

[Tim Graham]

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.

[Josh Smeaton]

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.

[James Addison]

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.

[Simon Charette]

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())))

[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.

[Simon Charette]

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.