[Home] You are not logged in. Login here

Just my stuff

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.

Anonymous Anonymous said...

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:

select name, array(select seq_id from produk where name=p1.name order by seq_id) as id_of_duplicates from produk p1 group by name order by name;

More flexible/powerful than GROUP_CONCAT().

You can convert the array to string if you want using array_to_string() function.

Sat Oct 25 05:47:40 +0200 2008
Anonymous Shadow said...

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.

Mon Jul 04 15:21:54 +0200 2011
Anonymous Brian Anderson said...

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!

Fri Oct 28 22:27:54 +0200 2011
Anonymous ojika said...

Thanks

Thu Jan 12 17:28:47 +0100 2012
 
Comments for this post have been disabled