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;

FYI, concat_ws will be in PostgreSQL 9.1: http://developer.postgresql.org/pgdocs/postgres/functions-string.html — Peter Eisentraut, Dec 24 '10 at 12:41
.. but in Postgres `concat_ws()` is *not* an aggregate function. Use `string_agg()` instead. See below. — Erwin Brandstetter, Jan 14 '14 at 19:55

score 17 · Accepted Answer · edited Jan 03 '14 at 06:33

17

For PostgreSQL 8.4 and above:

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

edited Jan 03 '14 at 06:33

ADTC

8,174
5
63
86

answered Dec 22 '09 at 00:18

Milen A. Radev

57,132
21
101
108

score 8 · Answer 2 · answered Apr 12 '13 at 19:10

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;

score 2 · Answer 3 · edited Jan 03 '14 at 06:32

2

Without array_agg (before 8.4), you can use:

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

edited Jan 03 '14 at 06:32

ADTC

8,174
5
63
86

answered Dec 26 '09 at 12:26

Daniel Vérité

53,594
14
124
144

score 0 · Answer 4 · edited Jan 03 '14 at 06:37

0

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='{}'
);

edited Jan 03 '14 at 06:37

ADTC

8,174
5
63
86

answered Apr 08 '11 at 21:18

nookni

303
3
6

score -1 · Answer 5 · edited Dec 08 '15 at 06:38

-1

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').

edited Dec 08 '15 at 06:38

Stefan Ferstl

4,875
3
29
40

answered Dec 07 '15 at 20:16

qaniel

1
1

Postgresql - concat_ws like function?

5 Answers5

Linked