A common Postgres “gotcha” I’ve run into before is the handling of NULL values. Equality comparisons in Postgres to NULL are neither true nor false.
Consider this data set:
sample_table
| id | name | boolean_field |
|---|---|---|
1 | Sample A | True |
2 | Sample B | False |
3 | Sample C | NULL |
In Postgres, if we perform SELECT * FROM sample_table WHERE boolean_field = TRUE;, we’ll get our expected one row.
| id | name | boolean_field |
|---|---|---|
1 | Sample A | True |
That translates in Django to SampleTable.objects.filter(boolean_field=True).
Great, we get what we expect!
But now consider SampleTable.objects.all().exclude(boolean_field=True). We’d expect to get the 2nd and 3rd rows right?
Wrong! Django translates that query as SELECT * FROM sample_table WHERE NOT(boolean_field = True);. Since comparisons to NULL values are neither true nor false, this statement only returns the second row.
| id | name | boolean_field |
|---|---|---|
2 | Sample B | False |
I wish Django would translate this using the special IS NOT operator.
SELECT * FROM sample_table WHERE boolean_field IS NOT True;
That returns the rows we expect.