Tom Lane wrote:
"D. Dante Lorenso" <dante@xxxxxxxxxxx> 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
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
