> 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" cheers, WBL On Wed, Jul 29, 2009 at 3:40 PM, Sam Mason<sam@xxxxxxxxxxxxx> wrote: > On Wed, Jul 29, 2009 at 01:15:27PM +0000, Jasen Betts wrote: >> On 2009-07-23, Sam Mason <sam@xxxxxxxxxxxxx> wrote: >> > http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Attention_on_IS_NULL_and_IS_NOT_NULL_operators_for_composite_types >> > >> > is scary; even worse is that it was changed to be like this in 8.2 >> > because the standard says it should behave this way. What on earth were >> > they thinking when they defined the standard this way? >> >> since any comparson involving those tuples will return NULL true is the >> correct value for IS NULL > > I think you missed the point: > > 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? > >> if you are bothered by this behavior you are misusing NULL. > > I understand that this is the specified behavior, and hence PG is > correctly following the spec--but it still bothers me. > > -- > Sam http://samason.me.uk/ > > -- > Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- "Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
