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?
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`? |