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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
