Curious unnest behavior

From: Jeff Trout <threshar(at)real(dot)jefftrout(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Curious unnest behavior
Date: 2013-01-03 19:22:25
Message-ID: 9A0BFC9D-1983-45CC-94AA-F84DAE8B9B36@torgo.978.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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>

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Gauthier, Dave 2013-01-03 21:57:29 Re: alter default privileges problem
Previous Message Thomas Kellerer 2013-01-03 19:15:38 Re: Large number of rows in pg_type and slow gui (pgadmin) refresh