#32525: Class Cast throws DataError when column contains numeric string or
alphabet
string in postgres
-----------------------------------------+--------------------------------
Reporter: kygoh | Owner: nobody
Type: New feature | Status: new
Component: Uncategorized | Version: 3.0
Severity: Normal | Keywords: QuerySet.extra
Triage Stage: Unreviewed | Has patch: 0
Needs documentation: 0 | Needs tests: 0
Patch needs improvement: 0 | Easy pickings: 0
UI/UX: 0 |
-----------------------------------------+--------------------------------
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()}}}.
--
Ticket URL: <https://code.djangoproject.com/ticket/32525>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-updates/048.cdc1f79132deb8ea43f7eb7a6ec889c4%40djangoproject.com.
