Re: Need efficient way to do comparison with NULL as an option

Tom Lane wrote:
> "D. Dante Lorenso" <dante(at)lorenso(dot)com> writes:
>> I'm looking for an operator that will compare NULL with NULL and
>> evaluate as TRUE.
> regression=# select null IS NOT DISTINCT FROM 42;
> ?column?
> ----------
> f
> (1 row)
> regression=# select null IS NOT DISTINCT FROM null;
> ?column?
> ----------
> t
> (1 row)
> However, if you're expecting this to be real efficient (like, use an
> index), you're out of luck ...
>> If the value I'm comparing is 0, I want it to match the NULL values.
> [ raised eyebrow... ] Sir, you need to rethink your data
> representation.

Tom,

I don't understand why my index is not being used (other than you said so):

----------
SELECT COUNT(*)
FROM audio
WHERE (folder_id = ? AND ? IS NOT NULL)
OR (folder_id IS NULL AND ? IS NULL);

uses index when ? = 100 (as expected)
does NOT use index when ? = NULL (as expected)

----------
SELECT COUNT(*)
FROM audio
WHERE folder_id IS NOT DISTINCT FROM ?;

does NOT use index when ? = NULL (as expected)
does NOT use index when ? = 100 (NOT expected!) <-------------!!!

----------

So, although 'IS NOT DISTINCT FROM' is a lot more readable than my other
form, it's apparently not efficient. How can I get the efficiency and
still have the clarity?

-- Dante