PostgreSQL array_agg order

Table 'animals':

animal_name animal_type
Tom         Cat
Jerry       Mouse
Kermit      Frog

Query:

SELECT 
array_to_string(array_agg(animal_name),';') animal_names,
array_to_string(array_agg(animal_type),';') animal_types
FROM animals;

Expected result:

Tom;Jerry;Kerimt, Cat;Mouse;Frog
OR
Tom;Kerimt;Jerry, Cat;Frog;Mouse

Can I be sure that order in first aggregate function will always be the same as in second. I mean I would't like to get:

Tom;Jerry;Kermit, Frog;Mouse,Cat

Use an ORDER BY, like this example from the manual:

SELECT array_agg(a ORDER BY b DESC) FROM table;

If you are on a PostgreSQL version < 9.0 then:

From: http://www.postgresql.org/docs/8.4/static/functions-aggregate.html

> In the current implementation, the order of the input is in principle unspecified. Supplying the input values from a sorted subquery will usually work, however. For example: > > SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;

So in your case you would write:

SELECT
array_to_string(array_agg(animal_name),';') animal_names,
array_to_string(array_agg(animal_type),';') animal_types
FROM (SELECT animal_name, animal_type FROM animals) AS x;

The input to the array_agg would then be unordered but it would be the same in both columns. And if you like you could add an ORDER BY clause to the subquery.

According to Tom Lane:

> ... If I read it right, the OP wants to be sure that the two aggregate functions will see the data in the *same* unspecified order. I think that's a pretty safe assumption. The server would have to go way out of its way to do differently, and it doesn't.

> ... 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.

So I think it's fine to assume that all the aggregates, none of which uses ORDER BY, in your query will see input data in the same order. The order itself is unspecified though (which depends on the order the FROM clause supplies rows).

Source: PostgreSQL mailing list

Do this:

SELECT 
    array_to_string(array_agg(animal_name order by animal_name),';') animal_names,
    array_to_string(array_agg(animal_type order by animal_type),';') animal_types
FROM 
    animals;