Django postgres query results getting different results for different database fields

Question

I have 2 simple tables in PostgreSQL.

Table 1 (which saves number in Varchar):

class Numbers_char_model(models.Model):

    number = models.CharField(max_length=256, blank=True, null=True)

Table 2 (which saves number in integer):

class Numbers_int_model(models.Model):

    number = models.IntegerField(blank=True, null=True)

The data in both tables are same.

id   number

1   ->  0

2   ->  20

3   ->  40

4   ->  70

5   ->  110

6   ->  150

When I hit the below queries, they both gives different results.

def number_query(request):

    ax_int = Numbers_int_model.objects.filter(number__lte='20')
    ax_char = Numbers_char_model.objects.filter(number__lte='20')

ax_int output --> 0,20

ax_char output --> 0,20,110,150

Can some one clear this?

Shakil · Accepted Answer · 2019-03-09T13:47:46.573

1

This is because int comparison and string comparison is not same. String comparison works on per char to char. Here for your case four string's first char is '0', '2', '1', '1'. and 2 is greater than all of them. So this type of output.

For this case at first we need to cast number field to int then we do this. This is possible with extralink

 Numbers_char_model.objects.extra({'number':  "CAST(number as INT)"}).filter_by(number__lte=20)

Reference : link

edited Mar 09 '19 at 13:47

answered Mar 09 '19 at 13:01

Shakil

4,520
3
26
36

so is there any way without **type casting** to execute above query to get output like integer one – rahul.m Mar 09 '19 at 13:02
one of the solution is to manually loop through and appending result. I don't know about better approach. need to check through internet. – Shakil Mar 09 '19 at 13:07
@c.grey though looping is an option, but i think this is possible in db-level. I just updated my answer a bit. can you please check. – Shakil Mar 09 '19 at 13:43

score 0 · Answer 2 · answered Mar 09 '19 at 13:00

0

Numbers_char_model has a CharField. You can't do lesser than or equals with a string. Numbers_int_model works because the string '20' gets converted to an int by the Django ORM.

answered Mar 09 '19 at 13:00

dan-klasson

13,734
14
63
101

score 0 · Answer 3 · answered Mar 09 '19 at 14:36

You can cast a string to int without extra (which has been deprecated). The following should give you the expected results (though it's obviously smarter to use an IntegerField to begin with):

from django.db.models import IntegerField
from django.db.models.functions import Cast

ax_char = (
    Numbers_char_model.objects
    .annotate(number_int=Cast('number', IntegerField()))
    .filter(number_int__lte=20)
)

Django postgres query results getting different results for different database fields

3 Answers3