group_concat on postgresql

CREATE TABLE produk(seq_id serial NOT NULL,name character varying(100) NOT NULL) ;INSERT INTO produk (seq_id, name) VALUES (1, 'beer');INSERT INTO produk (seq_id, name) VALUES (2, 'in');INSERT INTO produk (seq_id, name) VALUES (3, 'beer');INSERT INTO produk (seq_id, name) VALUES (4, 'can');INSERT INTO produk (seq_id, name) VALUES (6, 'goods');INSERT INTO produk (seq_id, name) VALUES (7, 'goods');INSERT INTO produk (seq_id, name) VALUES (8, 'can');INSERT INTO produk (seq_id, name) VALUES (5, 'goods');

 

MySQL:

select name, group_concat(cast(seq_id as char)) as id_of_duplicatesfrom produkgroup by nameorder by name;

PostgreSQL:

create aggregate array_accum (sfunc = array_append,basetype = anyelement,stype = anyarray,initcond = '{}');CREATE OR REPLACE FUNCTION _group_concat(text, text)RETURNS text AS $$SELECT CASEWHEN $2 IS NULL THEN $1WHEN $1 IS NULL THEN $2ELSE $1 operator(pg_catalog.||) ',' operator(pg_catalog.||) $2END$$ IMMUTABLE LANGUAGE SQL;CREATE AGGREGATE group_concat (BASETYPE = text,SFUNC = _group_concat,STYPE = text);

first approach:

select name, array_accum(seq_id)from produkgroup by nameselect name, array_to_string(array_accum(seq_id), ',')from produkgroup by name;

second approach (mysql-compatible approach):

select name, group_concat(seq_id) as id_of_duplicatesfrom produkgroup by nameorder by name;

MySQL GROUP_CONCAT with ordering:

select name, group_concat(cast(seq_id as char) order by seq_id) as id_of_duplicatesfrom produkgroup by nameorder by name;

PostgreSQL equivalent:

select name, group_concat(distinct seq_id) as id_of_duplicatesfrom produkgroup by nameorder by name;

using customized sort:

select name, group_concat(seq_id) as id_of_duplicatesfrom(select name, seq_idfrom produkorder by name, seq_id) as xgroup by name

http://s2.diffuse.it/blog/show/10-group_concat_on_postgresql