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

Tom Lane wrote:
> "D. Dante Lorenso" <dante(at)lorenso(dot)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