PostgreSQL array_agg order

Question

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 the order in the first aggregated function will always be the same as in the second. I mean, what I would like to get:

 Tom;Jerry;Kermit, Frog;Mouse,Cat 

Answer 1

If you are in PostgreSQL version <9.0, then:

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

In the current implementation, the input order is, in principle, unspecified. However, using input values ​​from a sorted subquery will work. For example:

SELECT xmlagg (x) FROM (SELECT x FROM test ORDER BY y DESC) AS tab;

So in your case you should 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; 

Now the entry in array_agg will be unordered, but in both columns it will be the same. And if you like, you can add a sentence to the subquery <<21>.

Answer 2

Use ORDER BY, as this example from the manual :

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