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,999
5
68
93

answered Dec 22 '09 at 00:18

Milen A. Radev

60,241
22
105
110

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,999
5
68
93

answered Dec 26 '09 at 12:26

Daniel Vérité

58,074
15
129
156

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,999
5
68
93

answered Apr 08 '11 at 21:18

nookni

313
3
6

score 0 · Answer 5 · answered Sep 29 '22 at 14:47

this works for; the tag data must show that asset has several, that is, for asset 6, which has 2 tags, show them 1,2 being the table:

asest_id 1 1 1 6 6

tag_id 1 3 5 1 2

SELECT asset_tag.asset_id,asset_tag.tag_id, concat_ws(',',asset_tag.tag_id,asset_tag.tag_id) AS etiqueta

FROM public.asset_tag

--JOIN PUBLIC.imagen ON asset_tag.asset_id = imagen.asset_id

--WHERE imagen.asset_id = asset_tag.asset_id

GROUP BY asset_tag.asset_id,asset_tag.tag_id ;

score -1 · Answer 6 · 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

5,135
3
33
41

answered Dec 07 '15 at 20:16

qaniel

1
1

Postgresql - concat_ws like function?

6 Answers6

Linked