Re: An aggregate function on ARRAY

On Wed, Aug 11, 2010 at 8:42 AM, Rafal Pietrak <rafal(at)zorro(dot)isa-geek(dot)com> wrote:
> Hi,
>
> I've started using ARRAY data type recently, and I fell into the
> following problem:
>
> When I have a relatively large ARRAY (like [1:500]) takeing an aggregate
> function on its elements is not so easy. One has to iterate all the
> elements, like this:
>
> SELECT min(A[1]) as a1, min(A[2]) as a2, ...
>
> This is because aggregate functions are not defined on ARRAY types. Or
> may be there is an easier and more readable way to do that?

If you have a fixed number of elements across the entire table, you
can accomplish what I think you are trying to do by expanding all the
arrays in the table and regrouping based on generate_series(), but
this is a horribly inefficient way to go. Are you sure you aren't
looking at table design issue?

merlin