I am using a older version of Postgres 8.2.11. Can anyone tell me the equivalent of MySql's group_concat for this Postgres 8.2.11. I have tried array_accum , array_to_string , string_agg but it doesn't work in this version
-
possible duplicate of Postgresql GROUP_CONCAT equivalent?Himanshu– Himanshu2013-05-09 06:09:38 +00:00Commented May 9, 2013 at 6:09
-
@hims056 cant you read the question properly i am asking for 8.2.11 and the possible duplicate question you are showing is for 8.4Ghostman– Ghostman2013-05-09 06:13:10 +00:00Commented May 9, 2013 at 6:13
-
Possible duplicate question is not for 8.4. It's answer is for 8.4.Himanshu– Himanshu2013-05-09 06:14:54 +00:00Commented May 9, 2013 at 6:14
-
@hims056 yup but i need for 8.2.11... so i couldnt find my answer can u give the answer for 8.2.11 pleaseGhostman– Ghostman2013-05-09 06:15:52 +00:00Commented May 9, 2013 at 6:15
-
@hims056 none of the answers specified in your possible duplicate question doesnt work on 8.2.11.... so please don't try to close the question???Ghostman– Ghostman2013-05-09 06:19:10 +00:00Commented May 9, 2013 at 6:19
|
Show 1 more comment
1 Answer
The "not quite duplicate" in the comments should point you in the right direction: create your own aggregate function. First you'll need a non-aggregate string concatenation function, something like this:
create function concat(t1 text, t2 text) returns text as $$
begin
return t1 || t2;
end;
$$ language plpgsql;
Then you can define your own aggregate version of that function:
create aggregate group_concat(
sfunc = concat,
basetype = text,
stype = text,
initcond = ''
);
Now you can group_concat all you want:
select group_concat(s)
from t
group by g
I dug this out of my archives but I think it should work in 8.2.
Keep in mind that 8.2 is no longer supported so you might want to upgrade to at least 8.4 as soon as possible.
7 Comments
Ghostman
i need commas in between the group concat result
mu is too short
@soul: If you want commas in the result then you could modify
concat to include commas and then add a finalfunc to the create aggregate which strips off the leading comma with a simple substring call. You might also want to read the manual about how NULLs are handled and adjust your aggregate to get the behavior you want.Ghostman
the problem with the query now is that it does not take the null values while group concat ... can u give a function which can replace null values with 0
mu is too short
@soul: You can use
coalesce for that.Ghostman
my outputs shows in this manner ` 1000046 smart lite twister 20W 8901399132209 191.08 {213.04} {1000001} 1000047 PARLE 20-20 BUTTER COOKIES(50GM) 8901719904431 4.46 {4.73,4.73} {1000001,1000002}`
|