Opened 3 years ago

Last modified 5 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: 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 (6)

comment:1 Changed 3 years ago by 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

comment:2 Changed 3 years ago by 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.

comment:3 Changed 3 years ago by 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.

comment:4 Changed 3 years ago by Tim Graham

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

comment:5 Changed 5 months ago by 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.

Last edited 5 months ago by James Addison (previous) (diff)

comment:6 Changed 5 months ago by 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())))
Note: See TracTickets for help on using tickets.
Back to Top