Re: is there a way to deliver an array over column from a query window?

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Rafał Pietrak <rafal(at)zorro(dot)isa-geek(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: is there a way to deliver an array over column from a query window?
Date: 2013-04-25 22:25:24
Message-ID: CAHyXU0yWePNxpD-UkyKz4cRbbVDtJCs++6QYi72fvUZeQRxQKQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Thu, Apr 25, 2013 at 1:30 PM, Rafał Pietrak <rafal(at)zorro(dot)isa-geek(dot)com> wrote:
> W dniu 04/25/2013 03:44 PM, Merlin Moncure pisze:
>
> On Wed, Apr 24, 2013 at 2:44 AM, Rafał Pietrak <rafal(at)zorro(dot)isa-geek(dot)com>
> wrote:
>
> W dniu 03/24/2013 12:11 PM, Rafał Pietrak pisze:
>
> W dniu 03/24/2013 12:06 PM, Misa Simic pisze:
>
> maybe,
>
> SELECT DISTINCT issuer,amount, array_agg(REFERENCE) over (partition by
> invoice_nr) from invoices;
>
>
> RIGHT. Thenx. (and the first thing I did, I've read the doc on
> array_agg().... what stress makes from people :(
>
>
> Actually, I have a problem with that (which I haven't noticed earlier
> because the data I'm having, don't have to many "duplicates" that cause it).
> The problem is, that:
> --------------------------------------
> SELECT DISTINCT a, b, c, array_agg(k.d) OVER (PARTITION BY k.c ) FROM testy
> k where k.e <> 'email' and k.c='1035049' ;
> a | b | c | array_agg
> ------+----------+---------+-------------------------------
> 1035 | 10410053 | 1035049 | {9902031328529,5951948640868}
> ---------------------------------------
>
> is _almost_ fine. But I actually need to have control over the order in
> which the array gathered its values. So I try:
> ------------------------------------
> SELECT DISTINCT a, b, c, array_agg(k.d) OVER (PARTITION BY k.c ORDER BY k.e)
> FROM testy k where k.e <> 'email' and k.c='1035049' ;
>
> you are aware of in-aggregate ordering (not completely sure if it
> meets your use case?
>
> select array_agg(v order by v desc) from generate_series(1,3) v;
>
> also, 'distinct'
> select array_agg(distinct v order by v desc) from (select
> generate_series(1,3) v union all select generate_series(1,3)) q;
>
>
> No, I don't (manual:
> http://www.postgresql.org/docs/9.1/static/tutorial-window.html, have just
> one word "distinct" on that page, and it's not in the above context). And I
> cannot duplicate the above:
> --------------------------------------------
> # select array_agg(distinct v order by v desc) from (select
> generate_series(1,3) v union all select generate_series(1,3)) q;
> ERROR: syntax error at or near "order"
> LINE 1: select array_agg(distinct v order by v desc) from (select ge...
> --------------------------------------------
>
> Did I miss something??

This feature was added w/9.0. This means you are on 8.4. Time to upgrade...

merlin

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sergey Konoplev 2013-04-25 22:34:19 Open transaction with 'idle' (not 'idle in transaction') status
Previous Message Tom Lane 2013-04-25 21:06:29 Re: Simple SQL INSERT to avoid duplication failed: why?