Re: The semantics of (NULL,NULL) vs NULL

On 2 August 2012 17:47, Ingmar Brouns <swingi(at)gmail(dot)com> wrote:
> As I noted that (null,null) is null, I thought why put (null,null) in an
> array when that is the same as putting null in there.
> However, when trying to unnest that array I got an error when using null
> instead of the tuple. I experimented a bit, and
> read the documentation on row and array comparison, but I could not find
> documentation explaining the results below.
>
> create type int_tuple as (a int, b int);
> CREATE TYPE
> =# select (null,null)::int_tuple is null;
> ?column?
> ----------
> t
> (1 row)

Without commenting on the specifics of your test-case, I don't think
any thorough reading of the SQL standard would leave the reader with
the impression that the behaviour of SQL NULL is consistent with some
simple axiom that can be generalised from. I found this blogpost to be
insightful:

http://thoughts.j-davis.com/2009/08/02/what-is-the-deal-with-nulls/

--
Peter Geoghegan http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training and Services