Re: why the need for is null?

Quoting Martijn van Oosterhout <kleptog(at)svana(dot)org>:

> Annoying, not really. It's actually extremely useful. It's useful having a
> value which is never equal to anything else, not even itself. If you use it
> to represent "unknown" it will work for you. If you try to use it for
> anything else, it will bite you.

I need it to represent "empty" because the field in question is a foreign key to
another table. If it represented "unknown" the foreign key should block it as
it could not possible know if that "unknown" value was valid. But I can't argue
against the SQL standard of course.

> You could create a new operator, but that means you'll have difficulty
> moving it to any database that doesn't have that operator (which is most of
> them).

Any commercial database vendor would be happy to make such a feature just for
that reason: to lock me in to their database :-). I do not try to stay database
neutral, and use lots of other features that will only work in postgresql.

> If you want it to match perhaps you should forget NULL and use '' (zero
> length string) instead.

Then I need to have a meaningless entry in the foreign table, and fill my code
with special cases that filter out that fake entry before showing the data to
the user.

Besides who said I didn't want to allow the empty string as valid data? This
would be even more an issue if the field was a nummeric, where any nummeric
value is ok. If I can not use NULL to represent "empty" or "not applicateable"
I would have to make a special field that tells me if I should ignore the
previous field or not. Does not sound reasonable when NULL works fine for just
that.

The best compromise I found so far is this "X=Y or X is null and Y is null"
construct. Just looks hard to understand and cumpersome for someone which is
not expert on this issue.

Baldur

----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.