CODEWITHSUNDEEP
pythondjangopostgresqldjango-views
rahul.m
rahul.m

Reputation: 5854

Django postgres query results getting different results for different database fields

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?

Upvotes: 0

Views: 295

Answers (3)

Endre Both
Endre Both

Reputation: 5730

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

Upvotes: 0

Shakil
Shakil

Reputation: 4630

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

Upvotes: 1

dan-klasson
dan-klasson

Reputation: 14190

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.

Upvotes: 0

Related Questions