Cast to FloatField throws error "django.db.utils.DataError: invalid input syntax for type double precision: "true"

Using Django
1

In order to solve a performance problem (which is solved!) i am not facing another challenge handling empty values or empty queryset? The old way was to iterate each value with a loop and if statement to convert to float and evaluate each record which was taking too long to complete and failing as a timeout.

I have a table that stores values that have parent/child relationship

class InspectionDetails(models.Model):
master_id = models.ForeignKey("InspectionMaster", verbose_name="InspectionMaster",
                              on_delete=models.CASCADE, related_name='details', default=3)
category_id = models.ForeignKey(
    "InspectionCategory",  on_delete=models.CASCADE)
# models.CharField("Item Id", max_length = 200)
item_id = models.ForeignKey("ItemInCategory",  on_delete=models.CASCADE)
item_value = models.CharField("Item value", max_length=500,db_index=True)
item_image = models.FileField(
    upload_to=None, max_length=100, blank=True)

class Meta:
    verbose_name = "Detail"
    verbose_name_plural = "Details"

def __str__(self):
    return self.category_id.category

The field item_value is a generic field that i can use to store text or a value. HTML format decides the validation on the frontend.
Once this value is stored I have a query that will do some comparison to find values that match a criteria and to do simple count/ display value.

b1_error_messages = {'BN': 'B-N voltage outside limits - report to TNB/SESB.',
                             'YN': 'Y-N voltage outside limits - report to TNB/SESB.',
                             'RN': 'R-N voltage outside limits - report to TNB/SESB.'}

        b2_error_messages = {'R': 'R-phase load high - reduce/rebalance.',
                             'Y': 'Y-phase load high - reduce/rebalance.',
                             'B': 'B-phase load high - reduce/rebalance.'}

        b3_error_messages = {
            'PF': 'Low PF reading - rectify to avoid penalty.'}
        all_errors = [b1_error_messages,
                      b2_error_messages, b3_error_messages]


        details = InspectionDetails.objects.filter(
                    master_id__add_date__range=getstartq(self),item_id__errortype=errtype).annotate(itemval = Cast('item_value',output_field=FloatField(default=0.0)))

        q1 = details.filter(item_id__items__in=b1_error_messages.keys()).filter(Q(itemval__lt=216)|Q(itemval__gt=253))
        if settings.DEBUG:
            print(q1)

This query works great IF there is data to manipulate. If there is no data the error below is thrown. How do i handle this error in Django.
Looking for advise to:

  1. Exception handling to tell Django to return and empty queryset rather than an error

  2. Changes to the model required?
    Some other suggestion. This is literally killing my project.
    The error I am getting is as below:

    psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type double precision: “true”
    … multiple line errors

    The above exception was the direct cause of the following exception:
    django.db.utils.DataError: invalid input syntax for type double precision: “true”

2

I would definitely fix the model.
It’s a really bad database design to overload fields with data of different data types.

Also, as a side note, when posting code here, please enclose it between lines consisting of three backtick - ` characters to maintain and display proper formatting. (This means you’ll have 1 line of ```, followed by your code, followed by another line of ```. This will make your code fragments a lot easer to read.)
Example:

# The previous line is ```
def function(self):
    return self
# The next line is ```
3

The app is supposed to be generic app to cater for different types of data. The only time we actually need this is to create reports based on custom logic. Hence the issue i am facing right now.
Is there a way to trap this error and execute an alternate query? or something like this? any ideas would be most welcome

4

That’s fine, I understand that, but that’s no excuse for a poorly designed table.

I’ve got one project where we use an ID/key/value table structure, where for the value we have different columns, one for each data type. (We have separate columns for Boolean, Integer, Decimal, Character, and JSON data types. The “key” column identifies which column is to be populated, and triggers and stored procedures ensure that the desired constraints are adhered to.)

5

Well I have a related field that captures the type of data that I’m storing for each row that’s saved. It Number, Text date etc to allow rendering and validation on the front end. Code might be a more complicated to insert in the right column. But even if I were to create individual fields my results don’t return a result because it’s not there. So how would I know to Cast and Not to Cast in this scenario. I think if there’s a way to catch this exception and handle it would be enough. Unfortunately I can’t seem to be able to do that.

6

In a properly designed table, you don’t cast. You either have a value of the appropriate type or null - and you add a filter to ensure that the results you retrieve are not null for the column you are interested in.