0

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

6
  • possible duplicate of Postgresql GROUP_CONCAT equivalent? Commented 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.4 Commented May 9, 2013 at 6:13
  • Possible duplicate question is not for 8.4. It's answer is for 8.4. Commented 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 please Commented 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??? Commented May 9, 2013 at 6:19

1 Answer 1

1

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.

Sign up to request clarification or add additional context in comments.

7 Comments

i need commas in between the group concat result
@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.
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
@soul: You can use coalesce for that.
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}`
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.