Skip site navigation (1) Skip section navigation (2)

Re: select a list of column values directly into an array

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Derrick Rice <derrick(dot)rice(at)gmail(dot)com>
Cc: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: select a list of column values directly into an array
Date: 2010-07-31 19:36:43
Message-ID: AANLkTin9c2PTwxWsuWmS0=n+kO8U3Vh-R431O9FwrWJg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
On Fri, Jul 30, 2010 at 11:50 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Fri, Jul 30, 2010 at 10:34 AM, Derrick Rice <derrick(dot)rice(at)gmail(dot)com> wrote:
>> Is it possible to use the ARRAY(select ...) syntax as a substitute for
>> array_agg on versions of postgresql that don't have it? (8.2)  It works
>> simply enough when only selecting a single column, but if I need to group by
>> some other column, I'm not clear how I'd go about doing that.
>>
>> For example, write the following in ARRAY(select...) form.
>
> yup...we've had array() for ages (I think -- see below).
>
> now, you've always been able to do array aggregation in userland --
> it's been in the docs as example since I can remember (see here:
> http://www.postgresql.org/docs/8.2/static/xaggr.html)
>
> The old school array_accum however is much slower than the newer
> array_agg. (which in turn is just a tiny bit slower than array()
> discounting grouping effects).  So you _definitely_ want to use
> array() if you don't require aggregate grouping features in older
> postgres versions.
>
> hm. I looked for the documentation for array(select...) to figure out
> exactly when it was introduced, couldn't find it.  Anyone know
> if/where this is documented?

I found it -- array() syntax is documented in array_constructor
portion of syntax.sgml.  It's been in postgres at least since 7.4,
which is as far back as I checked.

http://www.postgresql.org/docs/8.2/interactive/sql-expressions.html#SQL-SYNTAX-ARRAY-CONSTRUCTORS

merlin

In response to

pgsql-general by date

Next:From: Joshua TolleyDate: 2010-08-01 04:16:57
Subject: Re: PostgreSQL and distributed transactions
Previous:From: Devrim GÜNDÜZDate: 2010-07-31 10:03:46
Subject: Re: Centos 5.5 and Postgres 9.0.beta3 install errors

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group