The semantics of (NULL,NULL) vs NULL - Mailing list pgsql-general

Hi,

I have a question.

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)

=# select array[null::int_tuple] = array[null::int_tuple];
 ?column?
----------
 t
(1 row)

as the documentation states: 'Array comparisons compare the array contents element-by-element'
Taking into account the results above I would expect the following to be true

=# select array[(null,null)::int_tuple] = array[null::int_tuple];
 ?column?
----------
 f
(1 row)

apparently (null,null) is has more information then just null:

=# select null::int_tuple;
 int_tuple
-----------
 
(1 row)


=# select (null,null)::int_tuple;
 row
-----
 (,)
(1 row)


=# select * from  unnest(array[null::int_tuple]);
ERROR:  function returning set of rows cannot return null value

=# select * from unnest(array[(null,null)::int_tuple]);
 a | b
---+---
   | 
(1 row)


Can anyone explain why:


create type int_tuple as (a int, b int);
CREATE TYPE
=# select (null,null)::int_tuple is null;
 ?column?
----------
 t
(1 row)

and not

=# select array[(null,null)::int_tuple] = array[null::int_tuple];
 ?column?
----------
 f
(1 row)


Thanks in advance,

Ingmar


                                                   version                                                   
--------------------------------------------------------------------------------------------------------------
 PostgreSQL 9.1.1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.6.1 20110908 (Red Hat 4.6.1-9), 64-bit
(1 row)