[Django] #32525: Class Cast throws DataError when column contains numeric string or alphabet string in postgres
Django
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.
Django
string in postgres
Reporter: kygoh | Owner: nobody
Type: New feature | Status: closed
Component: Uncategorized | Version: 3.0
Severity: Normal | Resolution: wontfix
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
Changes (by Mariusz Felisiak):
* status: new => closed
* resolution: => wontfix
Comment:
Thanks for the ticket, however it's an issue in your data not in Django.
Moreover, it's unexpected to prevent/ignore data errors.
--
Ticket URL: <https://code.djangoproject.com/ticket/32525#comment:1>
Django
string in postgres
Reporter: kygoh | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution: wontfix
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
Changes (by Mariusz Felisiak):
* component: Uncategorized => Database layer (models, ORM)
--
Ticket URL: <https://code.djangoproject.com/ticket/32525#comment:2>
Django
string in postgres
Reporter: kygoh | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by kygoh):
* status: closed => new
* resolution: wontfix =>
Comment:
Replying to [comment:1 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
--
Ticket URL: <https://code.djangoproject.com/ticket/32525#comment:3>
Django
string in postgres
Reporter: kygoh | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: 3.0
(models, ORM) |
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
Changes (by Mariusz Felisiak):
* status: new => closed
* resolution: => wontfix
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/32525#comment:4>
Django
string in postgres
Reporter: kygoh | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution: wontfix
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by 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()
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/32525#comment:5>
Django
string in postgres
Reporter: kygoh | Owner: nobody
Type: New feature | Status: closed
Component: Database layer | Version: 3.0
(models, ORM) |
Severity: Normal | Resolution: wontfix
| Unreviewed
Has patch: 0 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Comment (by kygoh):
Replying to [comment:5 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/32525#comment:6>