Django filter for all items which contain at least one null value

An answer elsewhere on SO provides the Postgresql NOT(<table> IS NOT NULL) query as an option to find all rows in a table containing one or more null values. How would one apply this query in the Django ORM without writing raw SQL? Or is this a case where raw SQL is required?

I couldn’t find an example of such as query in the latest Django docs.

For reference, the example shown in the linked answer is as follows:

SELECT * FROM t;
┌────────┬────────┐
│   f1   │   f2   │
├────────┼────────┤
│ (null) │      1 │
│      2 │ (null) │
│ (null) │ (null) │
│      3 │      4 │
└────────┴────────┘
(4 rows)

SELECT * FROM t WHERE NOT (t IS NOT NULL);
┌────────┬────────┐
│   f1   │   f2   │
├────────┼────────┤
│ (null) │      1 │
│      2 │ (null) │
│ (null) │ (null) │
└────────┴────────┘
(3 rows)

Answer

You can use extra to add additional WHERE clauses to your query

Model.objects.extra(where=['NOT (%s IS NOT NULL)'], params=[Model._meta.db_table])

Advertisement

Answer