What is the equivalent for PostgreSQL GROUP_CONCAT?

Question

I want to pull one row per id with field values concatenated from my table.

Let’s say my table looks like as following:

TM67 | 4 | 32556 

TM67 | 9 | 98200 

TM67 | 72 | 22300 

TM99 | 2 | 23009 

TM99 | 3 | 11200

And II want output as:

TM67 | 4,9,72 | 32556,98200,22300 

TM99 | 2,3 | 23009,11200

I was able to use the aggregate function GROUP_CONCAT in MySQL, but this is not working here. Is there an equivalent for PostgreSQL, How can I accomplish this?

Answer 1

Version 8.4 is actually a good version to start with:

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

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

CREATE AGGREGATE array_agg (anyelement) (

sfunc = array_append,

stype = anyarray, 

initcond = '{}' 

);

This clarification is taken from PostgreSQL documentation.

Clarifications:

• Whenever you cast an array to text is that the resulting string starts and ends with curly braces. Those braces can be removed by some method if you do not need them. 

• 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,  so incorrect numbers of elements are displayed 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)" basically generates an error if value_field is an integer. "string_agg(value_field::text)" is required. The array_agg() method requires only one cast after the aggregation instead of a cast per value.

If you want to get more insights into SQL, checkout this SQL Course from Intellipaat.