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

Tom Lane wrote:
> "D. Dante Lorenso" <dante@lorenso.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