Re: Curious unnest behavior

I have to say, this seems straightforward to me. An array with N elements
gets N rows in the result set. I'm curious what other behavior would be
more reasonable.

On Thu, Jan 3, 2013 at 11:22 AM, Jeff Trout <threshar(at)real(dot)jefftrout(dot)com>wrote:

> I just ran into an interesting thing with unnest and empty arrays.
>
> create table x (
> a int,
> b int[]
> );
>
> insert into x(a,b) values (1, '{}');
> insert into x(a,b) values (1, '{}');
> insert into x(a,b) values (1, '{}');
>
> select a, b from x;
> select a, unnest(b) from x;
>
> insert into x(a,b) values (2, '{5,6}');
> select a, unnest(b) from x;
>
> drop table x;
>
> gives me:
> CREATE TABLE
> INSERT 0 1
> INSERT 0 1
> INSERT 0 1
> a | b
> ---+----
> 1 | {}
> 1 | {}
> 1 | {}
> (3 rows)
>
> a | unnest
> ---+--------
> (0 rows)
>
> INSERT 0 1
> a | unnest
> ---+--------
> 2 | 5
> 2 | 6
> (2 rows)
>
> DROP TABLE
>
> I can understand the likely reasoning behind the behavior but perhaps a
> note in the documentation about it might be of use for others that may get
> bit by this functionality. (especially given the structure of the query,
> had I been doing select * from unnest(arr) that would be more intuitive,
> but given the query structure of select with no where the results can be
> surprising.)
>
> thanks
>
> --
> Jeff Trout <jeff(at)jefftrout(dot)com>
>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>