Re: Need efficient way to do comparison with NULL as an option - Mailing list pgsql-general

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