array_agg problem
array_agg says it can take any type of arg, but this seems not to work
when the arg in an integer array.
create temp table x(name text, val integer[]);
insert into x values('a',array[1,2,3]);
insert into x values('b',array[3,4,5]);
select * from x;
select max(val), min(val) from x;
select array_agg(val) from x;
Well, everything works except the last statement. In 8.4 I get
ERROR: could not find array type for data type integer[]
I was hoping for [1,2,3,4,5] or at least [1,2,3,3,4,5] or even [[1,2,3],[3,4,5]]
Am I not understanding something?
Thanks,
TJ O'Donnell
On Fri, Aug 19, 2011 at 4:22 PM, TJ O'Donnell <tjo@acm.org> wrote:
array_agg says it can take any type of arg, but this seems not to work
when the arg in an integer array.create temp table x(name text, val integer[]);
insert into x values('a',array[1,2,3]);
insert into x values('b',array[3,4,5]);
select * from x;
select max(val), min(val) from x;
select array_agg(val) from x;Well, everything works except the last statement. In 8.4 I get
ERROR: could not find array type for data type integer[]I was hoping for [1,2,3,4,5] or at least [1,2,3,3,4,5] or even [[1,2,3],[3,4,5]]
Am I not understanding something?
yeah basically -- it's a common misunderstanding. array_agg converts
elements to arrays, but not arrays to array of dimension + 1. you
could do this:
select array(select unnest(val) from x);
to get [1,2,3,3,4,5].
Getting [[1,2,3],[3,4,5]] is a little harder:
create aggregate array_stack(int[])
(
sfunc=array_cat,
stype=int[]
);
postgres=# select array_stack(array[val]) from x;
array_stack
-------------------
{{1,2,3},{3,4,5}}
(1 row)
merlin
