Re: Are multiple array_aggs going to be in the same order?

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Mon, Apr 10, 2017 at 11:02 AM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> Sure, but isn't it fair to consider that an implementation artifact?

> So, the presence of ORDER BY in the aggregate function call is a PostgreSQL
> extension...

> It seems reasonable to declare that the order of the values in the
> generated array match whatever order the FROM clause supplies the rows. If
> that is not acceptable a PostgreSQL-specific ORDER BY modifier can be added
> which will cause an additional sort-and-scan of the input relation to occur
> (optimized across multiple column invocations when possible).

Yes, and in fact we documented the ORDER-BY-in-subselect solution back
before we had the ORDER-BY-in-aggregate feature. I don't remember exactly
where, but I'm sure it's still described somewhere. So it is documented
behavior that an aggregate without its own ORDER BY will see the rows in
whatever order the FROM clause supplies them.

I'm not very keen on recommending that the OP insert an ORDER BY into
each aggregate call, because that would cause a separate sort for each
aggregate (unless someone's improved that recently while I wasn't
looking).

regards, tom lane