id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
32525	Class Cast throws DataError when column contains numeric string or alphabet string in postgres	kygoh	nobody	"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()}}}."	New feature	closed	Database layer (models, ORM)	3.0	Normal	wontfix	QuerySet.extra		Unreviewed	0	0	0	0	0	0