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: developer.postgresql.org/pgdocs/postgres/functions-string.html — Peter Eisentraut
– Peter Eisentraut, Commented Dec 24, 2010 at 12:41
.. but in Postgres concat_ws() is not an aggregate function. Use string_agg() instead. See below. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jan 14, 2014 at 19:55

2 revs, 2 users 71% · Accepted Answer · 2014-01-03 06:33:35Z

17

For PostgreSQL 8.4 and above:

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

edited Jan 3, 2014 at 6:33

community wiki

2 revs, 2 users 71%
Milen A. Radev

Sign up to request clarification or add additional context in comments.

Comments

2 revs · Accepted Answer · 2023-12-15 23:50:05Z

Postgres 9.0 added the aggregate function string_agg() for the job:

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

The second parameter is the separator (similar to other aggregate functions).
See:

Concatenate multiple result rows of one column into one, group by another column

The string function concat_ws() (Postgres 9.1+) does 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 aggregate multiple columns:

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

2 revs, 2 users 73% · Accepted Answer · 2014-01-03 06:32:07Z

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 3, 2014 at 6:32

community wiki

2 revs, 2 users 73%
Daniel Vérité

Comments

3 revs, 2 users 55% · Accepted Answer · 2014-01-03 06:37:12Z

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 3, 2014 at 6:37

community wiki

3 revs, 2 users 55%
nookni

Comments

A izag alba · Accepted Answer · 2022-09-29 14:47:52Z

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 ;

3 revs, 2 users 67% · Accepted Answer · 2015-12-08 06:38:06Z

-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 8, 2015 at 6:38

community wiki

3 revs, 2 users 67%
Th3

Collectives™ on Stack Overflow

Postgresql - concat_ws like function?

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

Comments

Comments

Comments

Comments

Comments

Comments

Linked

Related