postgresqlarray-agg

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
Solution

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