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 GROUP_CONCAT, but that doesn't seem to work here... Is there an equivalent or another way to accomplish this?

Answer 1

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.

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)