According to PostreSQL documentation:

After a query has produced an output table (after the select list has been processed) it can optionally be sorted. If sorting is not chosen, the rows will be returned in an unspecified order.

With that in mind, is ORDER BY needed to preserve order when aggregating alphanumeric text created by generate_series?

For example, will numerical order always be preserved while aggregating text from generate_series like this:

select array_agg(f) from
(select 'f' || generate_series(1,3) as f ) s;

Or is ORDER BY necessary to preserve the numercial row order? Like this:

select array_agg(f order by n)
  from (select 'f' || n as f, n
          from generate_series(1,3) as t(n) ) s;

My guess: order is currently is preserved in the first example and it’s likely to stay that way in future versions of postgres. But based on the postgres docs, as well my second-hand understanding of the SQL standard, I probably shouldn’t rely on it.

Update

A reply from Vic Fearing, affirming ORDER BY is needed:

Tags:

Categories:

Published:    |    Updated: