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 <[email protected]>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 <[email protected]> > > > > > -- > Sent via pgsql-general mailing list ([email protected]) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
