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.
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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
