Wrapping a where clause to preserve rows with nulls

From: Adrian Garcia Badaracco <adrian(at)adriangb(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Wrapping a where clause to preserve rows with nulls
Date: 2024-12-19 03:47:15
Message-ID: CAE8z92GN0-5J=4q6RSsPGk_OP-Y_EHVNgQimTWwcq+BE+HGqRQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I have a query where I have to run a where clause generated by another
system (i.e., I can't modify that where clause. The where clause may return
`null`, but I actually want to keep rows that return `null` (and rows that
return `true` but not rows that return `false`).

I thought it would be as simple as wrapping in `(...) is not false` but
that seems to prevent index usage.

For example, let's say that given the table:

CREATE TABLE test_index (value INTEGER);
CREATE INDEX idx_value ON test_index(value);

And the predicate `value = 5000`, if I run the original query that excludes
rows where the predicate is null it uses the index:

SELECT *
FROM test_index
WHERE value = 5000;

But as soon as I tack on an `IS NOT FALSE` the index is not used:

SELECT *
FROM test_index
WHERE (value = 5000) IS NOT FALSE;

This was surprising to me. I was hoping this might be able to use the index.

Is there any way to include the rows where the predicate evaluates to null
while still using an index?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Garcia Badaracco 2024-12-19 04:12:36 Re: Wrapping a where clause to preserve rows with nulls
Previous Message Saul Perdomo 2024-12-19 00:02:30 Re: How to deal with dangling files after aborted `pg_restore`?