Postgresql - concat_ws like function?

Question

I'm having a first painful experience with postgresql, and the minute-challenge of the moment is :

How to perform a concat_ws in postgresql, to join several fields value from a group by :

select concat_ws(';',field_lambda) from table_lambda group by id;

Answer 1

For PostgreSQL 8.4 and above:

select ARRAY_TO_STRING(
    ARRAY_AGG(field_lambda),';'
) from table_lambda group by id;

Answer 2

Since PostgreSQL 9.0 (released September 2010), there is the aggregate function string_agg() to do what you seem to want:

SELECT string_agg(field1, ';') FROM tbl GROUP BY id;

Note, that the second parameter is the separator (similar to other aggregate functions) .

There is also the string function concat_ws() since PostgreSQL 9.1, that's otherwise doing the same as MySQL's concat_ws() (when not abused as aggregate function). It's particularly useful to deal with NULL values.

SELECT concat_ws(';', field1, field2, field3) FROM tbl

You could even combine both to aggreagate multiple columns any way you want.

SELECT id, string_agg(concat_ws(',', field1, field2, field3), ';') AS fields
FROM   tbl
GROUP  BY id;

Answer 3

Without array_agg (before 8.4), you can use:

SELECT array_to_string(
    ARRAY(SELECT field_lambda FROM table_lambda GROUP BY id), ';'
);

Answer 4

According to PostgreSQL wiki, you can emulate the PostgreSQL 8.4 array_agg function to get close to what you need.

CREATE AGGREGATE array_agg(anyelement) (
    SFUNC=array_append,
    STYPE=anyarray,
    INITCOND='{}'
);

Answer 5

Further people coming here for this problem, this method would not work with multiple columns(like concat_ws would) if you want to support multiple colums use

ARRAY_TO_STRING(ARRAY[$columns_string], 'my_delimiter').