Sam Mason wrote:
> 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.
But IS NULL applied to an array is useless to test if there are null values
inside, whereas this is apparently the whole point of IS NULL applied to
rows.
I mean:
select a is null from (select array[null]) x(a);
returns false, as well as:
select a is null from (select array[1]) x(a);
When applied to rows, if you consider that:
- is null applied to a row means that all columns are null
- is not null applied to a row means that all columns are not null
which is what the standard seems to dictate, then these operators make sense
and are probably useful in some situations.
Now there is the unfortunate consequence that (r is null) is not equivalent
to (not (r is not null)), yet it's not the standard's fault if "not all
values are null" is not the same as "all values are not null", that's just
set logic.
Maybe they could have made this easier for us by naming the operators
differently, such as "is entirely null" and "is entirely not null"
Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org