Re: array_agg problem

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