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 * 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)