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/