Re: comparing NEW and OLD (any good this way?)

On Wed, Aug 12, 2009 at 10:51:04AM +0200, Willy-Bas Loos wrote:
> > SELECT r IS NULL, r IS NOT NULL
> > FROM (VALUES (1,NULL)) r(a,b);
> >
> > returns FALSE for *both* columns. How can a row be both NULL *and*
> > non-NULL?
>
> Actually, the value is neither NULL, nor non-NULL.
> Part of it is NULL and part of it isn't so neither "IS NULL" is true,
> nor is "IS NOT NULL"

Nope, I still don't get it. Why treat rows specially? If this was
true, then what should:

SELECT a IS NULL, a IS NOT NULL
FROM (SELECT ARRAY [1,NULL]) x(a);

evaluate to? As "part of it" is NULL and part isn't then, by your
reasoning, it should return TRUE for both. PG doesn't and I think this
is much more useful behavior. The value itself is not unknown, it just
happens to contain some unknown values. Having a row that consists
entirely of NULL values being treated as NULL is OK, but some weird
halfway house is horrible. Standards' conforming, but still horrible.

--
Sam http://samason.me.uk/