Postgresql GROUP_CONCAT equivalent?

Question

I have a table and I'd like to pull one row per id with field values concatenated.

In my table, for example, I have this:

TM67 | 4  | 32556 TM67 | 9  | 98200 TM67 | 72 | 22300 TM99 | 2  | 23009 TM99 | 3  | 11200 

And I'd like to output:

TM67 | 4,9,72 | 32556,98200,22300 TM99 | 2,3    | 23009,11200 

In MySQL I was able to use the aggregate function GROUP_CONCAT, but that doesn't seem to work here... Is there an equivalent for PostgreSQL, or another way to accomplish this?

Answer 1

Since 9.0 this is even easier:

SELECT id,         string_agg(some_column, ',') FROM the_table GROUP BY id 

Answer 2

This is probably a good starting point (version 8.4+ only):

SELECT id_field, array_agg(value_field1), array_agg(value_field2) FROM data_table GROUP BY id_field 

array_agg returns an array, but you can CAST that to text and edit as needed (see clarifications, below).

Prior to version 8.4, you have to define it yourself prior to use:

CREATE AGGREGATE array_agg (anyelement) (     sfunc = array_append,     stype = anyarray,     initcond = '{}' ); 

(paraphrased from the PostgreSQL documentation)

Clarifications:

• The result of casting an array to text is that the resulting string starts and ends with curly braces. Those braces need to be removed by some method, if they are not desired.
• Casting ANYARRAY to TEXT best simulates CSV output as elements that contain embedded commas are double-quoted in the output in standard CSV style. Neither array_to_string() or string_agg() (the "group_concat" function added in 9.1) quote strings with embedded commas, resulting in an incorrect number of elements in the resulting list.
• The new 9.1 string_agg() function does NOT cast the inner results to TEXT first. So "string_agg(value_field)" would generate an error if value_field is an integer. "string_agg(value_field::text)" would be required. The array_agg() method requires only one cast after the aggregation (rather than a cast per value).