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
