Postgresql - concat_ws like function?

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_lamb开发者_开发问答da) from table_lambda group by id;

---

For PostgreSQL 8.4 and above:

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

---

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;

---

Without array_agg (before 8.4), you can use:

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

---

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

---

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 ;

---

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