Need efficient way to do comparison with NULL as an option
All,
I'm looking for an operator that will compare NULL with NULL and
evaluate as TRUE.
I have a BIGINT column which might contain NULL values. I want to pass
a value to compare with that column in my WHERE clause. If the value
I'm comparing is 0, I want it to match the NULL values. Here is a
sample query that I currently use:
SELECT *
FROM mytable
WHERE (col IS NULL AND NULLIF(?, 0) IS NULL) OR col = ?;
The '?' placeholders used in the query will receive the same value which
might be any one of the following: NULL, 0, 1, 2, 3, etc.
What I'd really like is an operator that will compare NULL with NULL and
evaluate as TRUE. Does that exist?
I tried solving this myself, so I have a stored proc like this:
--------------------
CREATE OR REPLACE FUNCTION "public"."is_equal_bigint" (in_val1 bigint,
in_val2 bigint) RETURNS boolean AS
$body$
BEGIN
-- both values are null
IF in_val1 IS NULL AND in_val2 IS NULL THEN
RETURN TRUE;
END IF;
-- values are the same
IF in_val1 = in_val2 THEN
RETURN TRUE;
END IF;
-- values are different
RETURN FALSE;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;
--------------------
I can use this function as follows:
SELECT *
FROM mytable
WHERE is_equal_bigint(col, NULLIF(?, 0)) IS TRUE;
But I worry that this will not allow my query to use any indexes on
'col' and will make all my queries slower.
Any recomendations for making my first query above more optimized and
still efficient? Does the operator I'm looking for exist?
-- Dante
D. Dante Lorenso wrote:
I'm looking for an operator that will compare NULL with NULL and
evaluate as TRUE.
I have a BIGINT column which might contain NULL values. I want to pass
a value to compare with that column in my WHERE clause. If the value
I'm comparing is 0, I want it to match the NULL values. Here is a
sample query that I currently use:
SELECT *
FROM mytable
WHERE (col IS NULL AND NULLIF(?, 0) IS NULL) OR col = ?;
The '?' placeholders used in the query will receive the same value which
might be any one of the following: NULL, 0, 1, 2, 3, etc.
What I'd really like is an operator that will compare NULL with NULL and
evaluate as TRUE. Does that exist?
Is this the answer?
SELECT *
FROM mytable
WHERE col IS NOT DISTINCT FROM NULLIF(?, 0);
-- Dante
"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.
regards, tom lane
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.
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,
Here's what I'm doing, tell me if I'm crazy:
The column I'm comparing to is 'folder_id'. The folder_id column is a
foreign key to a folder table. If folder_id is NULL, the row is not in
a folder.
If I want to find all items in a specific folder, I want:
SELECT *
FROM mytable
WHERE folder_id = 123;
But if I want to find all the items which are not in any folder, I want:
SELECT *
FROM mytable
WHERE folder_id IS NULL;
I don't have any folder_id 0, so on a URL I might do this:
http://xyz/page.php?fid=123
http://xyz/page.php?fid=0
If folder_id is 0, I do the NULL comparison.
SELECT *
FROM mytable
WHERE folder_id IS NOT DISTINCT FROM NULLIF(?, 0);
That seems to do what I want. Is it bad design? Something I'm missing
about indexing a NULL or something like that?
-- Dante
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
D. Dante Lorenso wrote:
But if I want to find all the items which are not in any folder, I want:
SELECT *
FROM mytable
WHERE folder_id IS NULL;I don't have any folder_id 0, so on a URL I might do this:
Why not just have fid undef? thus you can just say:
If folder_id is 0, I do the NULL comparison.
SELECT *
FROM mytable
WHERE folder_id IS NOT DISTINCT FROM NULLIF(?, 0);
SELECT *
FROM mytable
WHERE folder IS NULL;
Or have your PHP say...
if ($fid == 0) {
$fid == "IS NULL"
}
(my php is extremely rusty but I think you get the idea)
And then build out your where clause.
That seems to do what I want. Is it bad design? Something I'm missing
about indexing a NULL or something like that?
Sincerely,
Joshua D. Drake
Show quoted text
-- Dante
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
"D. Dante Lorenso" <dante@lorenso.com> writes:
Here's what I'm doing, tell me if I'm crazy:
The column I'm comparing to is 'folder_id'. The folder_id column is a
foreign key to a folder table. If folder_id is NULL, the row is not in
a folder.
Yup, you're crazy. The best interpretation of NULL according to the SQL
spec is that you don't know which folder the row is in.
If you are willing to reserve ID 0 as not being any real folder, then
folder_id = 0 would be a reasonable way to represent "it's not in a
folder". This is positive knowledge, entirely distinct from "I don't
know if it's in a folder, much less which one".
Now there is a small problem with that, which is that if you want to
have folder_id be a foreign key to a table of folders then it doesn't
work so well. But do not let yourself be tempted to use NULL as a
solution to that. What I'd suggest after a few seconds' thought is that
you create an explicit "unclassified" folder and put every "not in a
folder" row into the "unclassified" folder.
regards, tom lane
D. Dante Lorenso wrote:
But if I want to find all the items which are not in any folder, I want:
SELECT *
FROM mytable
WHERE folder_id IS NULL;I don't have any folder_id 0, so on a URL I might do this:
SELECT * FROM mytable WHERE COALESCE( folder_id, 0 ) = 0;
--
Lew