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)
