Wednesday, December 19, 2007

PostgreSQL GROUP_CONCAT

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


name | id_of_duplicates
----------+------------------
beer | 1,3
can | 4,8
goods | 5,6,7
in | 2



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


PostgreSQL is a lot more nicer, it allows you to define your own aggregate function. GROUP_CONCAT is not built-in, with user-defined aggregate, you can create one.

Labels: , , ,

36 Comments:

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.

11:45 AM

 
Blogger Prajwala said...

The comment is really what I want.
I feel using array() is more convenient that what is mentioned in the post.

6:13 PM

 
Anonymous Anonymous said...

Discount sustiva Now speman Low price exelon Side-effects verapamil Order kemadrin Visa/Mastercard/Amex/eCheck duphalac

1:53 AM

 
Anonymous Anonymous said...

Amiable post and this post helped me alot in my college assignement. Say thank you you on your information.

7:45 AM

 
Anonymous Anonymous said...

It is very prominent to shock a resemble adapted custody of all your ornaments pieces so that they mould quest of a lifetime. There are divers approaches and ways to clean weird types of jewels be it gold, euphonious, pearls, diamond or semiprecious stone stones. Outlined in this world are the individual ways around which you can take nurse of your accessories and maintain them shiny and new always.
[url=http://blackfriday2010.spruz.com/]Black Friday 2010[/url]

4:18 PM

 
Anonymous Anonymous said...

To be a good human being is to have a make of openness to the in the seventh heaven, an skill to group unsure things beyond your own restrain, that can take you to be shattered in unequivocally exceptionally circumstances as which you were not to blame. That says something exceedingly important about the condition of the honest autobiography: that it is based on a conviction in the uncertain and on a willingness to be exposed; it's based on being more like a weed than like a prize, something kind of feeble, but whose acutely particular beauty is inseparable from that fragility.
Appareils photo
[url=http://BoutiquePhotographique.com]Camescope[/url]

5:44 AM

 
Anonymous taboo stories free said...

She had three he had five and he didntseem to care what any of them saw. He also likes the name you chose for him.
incest children stories
free real incest stories
womens stories of beastiality
bondage locking shoes and boots stories
kinky sex stories
She had three he had five and he didntseem to care what any of them saw. He also likes the name you chose for him.

11:40 AM

 
Anonymous Anonymous said...

ttc clomid | buy clomid 100mg uk - buy clomid and nolvadex, how many days after clomid will i ovulate

12:26 AM

 
Anonymous Anonymous said...

generic name for clomid | can you buy clomid online - buying clomid no prescription, clomid and ovulation pain

11:30 PM

 
Anonymous Anonymous said...

clomid 100mg days 5 9 | buy clomid and nolvadex - safe buy clomid online, clomid 150 mg success

7:54 PM

 
Anonymous Anonymous said...

clomid without trigger shot | http://cheapclomidonline.jimdo.com/#5846 - clomid 50mg tablets, clomid for infertility

4:18 AM

 
Anonymous Anonymous said...

when to start clomid after cycle | http://buyclomidonline.webs.com/#75939 - clomid 50mg tablets, over the counter clomid

5:51 PM

 
Anonymous Anonymous said...

clomid success stories twins | [url=http://orderclomid.jimdo.com/#68939]hcg clomid[/url] - purchase clomid, dtgs second round of clomid

5:06 AM

 
Anonymous Anonymous said...


An intriguing discussion is definitely worth comment. I think that you should write more on this issue, it may not be a taboo subject but typically people don't talk about such topics. To the next! Best wishes!!

6:50 AM

 
Anonymous Anonymous said...

clomid multiple birth statistics | http://buyclomidcheap.webs.com/#19706 - buy clomid, clomid and alcohol consumption

5:51 PM

 
Anonymous Anonymous said...

www.blogger.com owner you are great

[url=http://luv-2-share-pics.tumblr.com]sexy girls pics[/url]

11:48 PM

 
Anonymous Anonymous said...

when do you ovulate on clomid 2 6 | 150 mg clomid - best place to buy clomid online, clomid twins percentage

5:03 AM

 
Anonymous Anonymous said...

clomid ttc | [url=http://purchaseclomid.jimdo.com/#44096]price of clomid without insurance[/url] - clomid tablets for sale, zmdl clomid early pregnancy symptoms

6:03 PM

 
Anonymous Anonymous said...

clomid ovary pain | medication clomid - clomid pills online, clomid and missed period

12:29 AM

 
Anonymous Anonymous said...

clomid use in men | [url=http://ordergenericclomid.webs.com/#57968]cost of clomid[/url] - where to buy clomid online no prescription, kbnz when to test for pregnancy on clomid

8:16 AM

 
Anonymous Anonymous said...

Asking questions are truly good thіng if you агe not unԁerstanding something
fully, hοwever this paragrаph gives рleasant undеrstanding
evеn.

Mу ωеbрage - avatar bluray Rip

6:17 PM

 
Anonymous Anonymous said...

Are you searching for BBW pics this blog is the right place for you!

6:05 PM

 
Anonymous Anonymous said...

Szukasz seksu bez zobowiazan ? Najwiekszy serwis randkowy dla doroslych, zajrzyj do nas

seks og³oszenia

8:01 AM

 
Anonymous Anonymous said...

They would not be able to get it done hassle-free connected with finish a have any shorter with the that are authorized in thse lending options are:. How can you create your credit popularity and get short term for an urgent situation if you have simply no credit to communicate of. However make sure that you pay off each the request a is often in fact one you then have a bad credit comes from far better home. sameday loans With these ideas you not only will stay on aim for with your grocery budget, but you currently have your meal plan already established.

10:44 AM

 
Anonymous Anonymous said...

Usually, these kinds of folks was once dependent on the mercy in their relatives and friends? What this plan does accomplish is hole your account while using the credit bureaus as well as makes it tougher to get whatever really in hot water you. There are several loans inside the wisely payday loans lenders that they health of the personal loan seekers. instant payday loans uk Sorts known as bad credit retired that occurs over the upcoming three and a half several years.

11:05 AM

 
Anonymous Anonymous said...

Great work, great site! Thank you!

3:32 PM

 
Anonymous Anonymous said...

Sew on the zips. Cut out two waste 3.75x12 inch plain textile. These will be your zip panel. Never the less, Stamping leather is among the funniest and simplest artistic tasks you can do. Embossed or tooled leather is also another terms used to refer to stamped leather. You should not get confused if you observe these terms.[url=http://www.burberrynj.com]Burberry bags[/url] Recycled And Biodegradable BagsGarbage bags made from plastic do not solve the problems associated with plastic bags filling landfills and causing ground pollution, But they do reduce the exact amount of discarded plastic by creating demand for plastic recycling and encouraging the recycling process to continue. Able to degrade bags are a more practical alternative. Majority of these kinds bags, Quite often made from corn, Disintegrate within months in landfills.Normal dosage during this period is 3000 milligrams per dayIf you do choose to make your own windmill blades, I suggest you take time to learn about the top three wind power conversion guides. With our top pick distinctively, They just lately added a video that teaches that exact topic. At any rate, Be sure to take the time to design your blades to fit the job.

1:57 AM

 
Anonymous Anonymous said...

karen millen sale uk It has everything complete, from its precise making to timely delivery. mulberry outlet uk

8:01 PM

 
Anonymous Anonymous said...

So as an alternative to walking at a continuous tempo for say an hour,
you'd probably alter issues up by alternating quick bursts of intense strolling for say a minute followed by a recovery time period with slower strolling soon after every single extreme period. Just continue to keep alternating for 20 minutes to start out and include time when you develop into a lot more match. You could use interval teaching with all phases on thegreen coffee bean extract to boost your final results.

Also once you do any form of work out it truly is strongly advised you drink plenty of water prior to for the duration of and following to keep your whole body properly hydrated.

10:09 PM

 
Anonymous Anonymous said...

Why visitors still make use of to read news papers when in this technological
globe everything is existing on net?

Also visit my website :: beim schwimmen musik hören

6:15 AM

 
Blogger mai samra said...


شركة تنظيف منازل بخميس مشيط
شركة تنظيف شقق بخميس مشيط
شركة رش مبيدات بخميس مشيط

4:19 AM

 
Blogger firoz mahmud said...

This comment has been removed by the author.

12:14 PM

 
Blogger firoz mahmud said...

This comment has been removed by the author.

3:39 PM

 
Blogger firoz mahmud said...

This comment has been removed by the author.

11:46 AM

 
Blogger firoz mahmud said...

This comment has been removed by the author.

10:58 AM

 
Blogger firoz mahmud said...

This comment has been removed by the author.

3:06 PM

 

Post a Comment

<< Home