#32525 closed New feature (wontfix)
Class Cast throws DataError when column contains numeric string or alphabet string in postgres
Reported by: | kygoh | Owned by: | nobody |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | 3.0 |
Severity: | Normal | Keywords: | QuerySet.extra |
Cc: | Triage Stage: | Unreviewed | |
Has patch: | no | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
Using the following queryset
InspectionDetails.objects.annotate( itemval = Cast('item_value', output_field=FloatField(default=0.0)) ).filter( Q(itemval__lt=216) | Q(itemval__gt=253) )
will generate the following SQL statement for postgres:
SELECT "inspectv1_inspectiondetails"."id", "inspectv1_inspectiondetails"."master_id_id", "inspectv1_inspectiondetails"."category_id_id", "inspectv1_inspectiondetails"."item_id_id", "inspectv1_inspectiondetails"."item_value", "inspectv1_inspectiondetails"."item_image", ("inspectv1_inspectiondetails"."item_value")::double precision AS "itemval" FROM "inspectv1_inspectiondetails" WHERE (("inspectv1_inspectiondetails"."item_value")::double precision < 216.0 OR ("inspectv1_inspectiondetails"."item_value")::double precision > 253.0)
However, item_value
may store numeric string as well as 'true' which will cause:
django.db.utils.DataError: invalid input syntax for type double precision: "true"
To overcome the problem, extra() QuerySet
modifier was used as follows:
q = InspectionDetails.objects.extra(where=['cast_to_numeric(item_value) < 216 or cast_to_numeric(item_value) > 253'])
where cast_to_numeric
is the following postgres function (source: https://stackoverflow.com/a/10307443):
create or replace function cast_to_numeric(text) returns numeric as $$ begin -- Note the double casting to avoid infinite recursion. return cast($1::varchar as numeric); exception when invalid_text_representation then return 0; end; $$ language plpgsql immutable;
I hope this edge case can be considered in the QuerySet API enhancement to allow removing extra()
.
Change History (6)
follow-up: 3 comment:1 by , 4 years ago
Resolution: | → wontfix |
---|---|
Status: | new → closed |
comment:2 by , 4 years ago
Component: | Uncategorized → Database layer (models, ORM) |
---|
comment:3 by , 4 years ago
Resolution: | wontfix |
---|---|
Status: | closed → new |
Replying to Mariusz Felisiak:
Thanks for the ticket, however it's an issue in your data not in Django. Moreover, it's unexpected to prevent/ignore data errors.
Unfortunately the item_value
column by design allows numeric as well as non-numeric string data. My request is not to prevent/ignore data errors but to not deprecate extra()
unless there's an alternative to handle such cases since Django documentation states:
This is an old API that we aim to deprecate at some point in the future
comment:4 by , 4 years ago
Resolution: | → wontfix |
---|---|
Status: | new → closed |
Please don't reopen closed tickets. QuerySet.extra()
will be deprecated in the future, however we don't have plans to do this in Django 4.0.
follow-up: 6 comment:5 by , 4 years ago
Also, you don't need to use extra()
you can use a custom function (if it already exists in your db):
class CastToNumeric(Func): function = 'cast_to_numeric' output_field = FloatField()
comment:6 by , 4 years ago
Replying to Mariusz Felisiak:
Also, you don't need to use
extra()
you can use a custom function (if it already exists in your db):
class CastToNumeric(Func): function = 'cast_to_numeric' output_field = FloatField()
Pardon my ignorance for reopening closed tickets. This suggestion is indeed of great help. Thank you.
Thanks for the ticket, however it's an issue in your data not in Django. Moreover, it's unexpected to prevent/ignore data errors.