Class Cast throws DataError when column contains numeric string or alphabet string in postgres

[kygoh]

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

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

[kygoh]

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

[Mariusz Felisiak]

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.

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

[kygoh]

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.