Django查询PostgreSQL：不同字段类型返回结果差异解惑

Great question—this is a classic gotcha that trips up a lot of developers, and it all comes down to how PostgreSQL handles comparisons for different data types.

Let's recap your setup first: #

• You've got two Django models:
  • Numbers_char_model: Stores numbers as a CharField (translates to PostgreSQL's varchar type)
  • Numbers_int_model: Stores numbers as an IntegerField (translates to PostgreSQL's integer type)
• Both tables hold the exact same values: 0, 20, 40, 70, 110, 150
• When running filter(number__lte='20'), the integer field returns 0, 20, while the char field returns 0, 20, 110, 150

Here's why the results differ: #

1. IntegerField (PostgreSQL integer type)

PostgreSQL treats these as pure numerical values. When you pass the string '20' to the __lte filter, Django and PostgreSQL automatically convert that string to the integer 20, then run a numerical comparison. So it's checking which values are numerically less than or equal to 20—which correctly narrows down to 0 and 20.

2. CharField (PostgreSQL varchar type)

For string fields, PostgreSQL uses lexicographical (dictionary-style) comparison instead of numerical logic. Strings are compared one character at a time, based on their ASCII values:

• Take '110' vs '20': The first character of '110' is '1', which has a lower ASCII value than '2' (the first character of '20'). So PostgreSQL considers '110' "less than" '20' in string terms.
• The same goes for '150'—its first character '1' is smaller than '2', so it gets included too.
• Only values starting with a character higher than '2' (like '40' or '70') get excluded from the results.

If you need numerical comparisons on a CharField #

If you have to keep the field as a CharField but want numerical filtering, you can cast the field to an integer in your query using Django's Cast function:

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

# This will give you the same result as the IntegerField query
ax_char = Numbers_char_model.objects.annotate(
    number_as_int=Cast('number', output_field=IntegerField())
).filter(number_as_int__lte=20)

This converts the varchar values to integers first, then runs the numerical comparison you expect.

内容的提问来源于stack exchange，提问作者rahul.m