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_duplicates from produk group by name order 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 CASE
WHEN $2 IS NULL THEN $1
WHEN $1 IS NULL THEN $2
ELSE $1 operator(pg_catalog.||) ',' operator(pg_catalog.||) $2
END
$$ IMMUTABLE LANGUAGE SQL;
CREATE AGGREGATE group_concat (
BASETYPE = text,
SFUNC = _group_concat,
STYPE = text
);
first approach:
select name, array_accum(seq_id) from produk group by name select name, array_to_string(array_accum(seq_id), ',') from produk group by name;
second approach (mysql-compatible approach):
select name, group_concat(seq_id) as id_of_duplicates from produk group by name order by name;
MySQL GROUP_CONCAT with ordering:
select name, group_concat(cast(seq_id as char) order by seq_id) as id_of_duplicates from produk group by name order by name;
PostgreSQL equivalent:
select name, group_concat(distinct seq_id) as id_of_duplicates from produk group by name order by name;
using customized sort:
select name, group_concat(seq_id) as id_of_duplicates from ( select name, seq_id from produk order by name, seq_id ) as x group by name
I found this here.
Btw, if you don’t mind not using a different syntax (not using GROUP_CONCAT() as in MySQL), you can easily achieve the same result using arrays:
More flexible/powerful than GROUP_CONCAT().
You can convert the array to string if you want using array_to_string() function.
One of the reasons why I like visiting your blog so much is because it has become a daily reference I can use Generic Viagra in order to learn new nice stuff. It’s like a curiosities box that surprises you over and over again.
Hi, listen, I’m pretty new on this blogosphere and Internet thing, so I don’t know if there’s a sort of “subscription” method that I can use Viagra Canada in order to receive notifications of your new entries…? Thing is I enjoy reading your blog a lot and I’d like to be up to date with your posts!
Thanks