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-09 13:47:46Z

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 9, 2019 at 13:47

answered Mar 9, 2019 at 13:01

Shakil

4,6683 gold badges28 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

rahul.m Over a year ago

so is there any way without type casting to execute above query to get output like integer one

Shakil Over a year ago

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 Over a year ago

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

dan-klasson · Accepted Answer · 2019-03-09 13:00:22Z

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 9, 2019 at 13:00

dan-klasson

14.3k14 gold badges67 silver badges106 bronze badges

Comments

Endre Both · Accepted Answer · 2019-03-09 14:36:22Z

0

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

answered Mar 9, 2019 at 14:36

Endre Both

5,8101 gold badge29 silver badges32 bronze badges

Collectives™ on Stack Overflow

Django postgres query results getting different results for different database fields

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related