Why `schema.field != true` will filter the record which field is nil

When i used the query with ecto:

query
|> where([q], q.field != true)

The result will be filtered the record both which q.field == true and q.field == nil.

To solve this, i need to use

query
|> where([q], q.field == false or is_nil(q.field)

But intuitively, nil should not be equal to true, so i don’t know it’s ecto’s problem or postgres’ problem.

This is an SQL thing. Comparisons with NULL don’t return a boolean value but unknown. The return value of NULL != true is unknown, which is then treated as falsy. Thus the rows are filtered out. This is why SQL has IS NULL and friends, which you use with is_nil(q.field) in Ecto.

There’s a lot of information about NULL on Wikipedia: Null (SQL) - Wikipedia

7 Likes

Thank you for your answer