Postgres aggregate alias for group_concat using string_agg

Question

I know that postgres does not have group_concat, but I want to emulate it for strings by using string_agg (or any other way that works).

I need to have the function called group_concat because of an inability to change legacy code.

How can I do this?

For what its worth, I also tried implementing group_concat using regular concat, and ran into an error there as well:

CREATE AGGREGATE group_concat (text) (sfunc = concat, stype=text)

error:

"function concat(text,text) does not exist"

postgresql.org/docs/current/static/sql-createfunction.html. However, I would recommend that you change the application rather than the database. — Gordon Linoff
– Gordon Linoff, Commented Feb 10, 2017 at 12:40
@GordonLinoff See update. Its not possible for policy reasons, though trust me, I would prefer that — soandos
– soandos, Commented Feb 10, 2017 at 12:41
@soandos: I don't think you have a choice. You might get away with simulating group_concat() but the MySQL syntax deviates in so many areas from standard SQL and the one used by Postgres that you won't get far with keeping the existing SQL statements as they are. The first things that come to mind are date or interval literals, the strange (not to say broken) "boolean" handling in MySQL or implicit type casts. — user330315
– user330315, Commented Feb 10, 2017 at 13:16
@a_horse_with_no_name I only need to define this for strings. I don't care about any other types — soandos
– soandos, Commented Feb 10, 2017 at 13:17
I am not talking about group_concat() for other types. I am talking about the huge differences in syntax between Postgres and MySQL. I can't imagine that you will succeed in keeping SQL statements written for MySQL without any changes in your code when you run that against Postgres. It simply won't work. — user330315
– user330315, Commented Feb 10, 2017 at 13:19

Abelisto · Accepted Answer · 2017-02-10 13:47:34Z

5

-- drop aggregate if exists group_concat(text);
CREATE AGGREGATE group_concat(text) (
  SFUNC=textcat,
  STYPE=text
);

select group_concat(x) from unnest('{a,b,c,d}'::text[]) as x;

textcat is the function used internally by the || operator:

CREATE OPERATOR ||(
  PROCEDURE = textcat,
  LEFTARG = text,
  RIGHTARG = text);

Update

To make the comma as the separator:

--drop aggregate if exists group_concat(text);
--drop function if exists group_concat_trans(text, text);

create or replace function group_concat_trans(text, text)
  returns text
  language sql
  stable as 
$$select concat($1,case when $1 is not null and $2 is not null then ',' end,$2)$$;

create aggregate group_concat(text) (
  sfunc=group_concat_trans,
  stype=text);

select group_concat(x) from unnest(array['a','b','c',null,'d']) as x;

╔══════════════╗
║ group_concat ║
╠══════════════╣
║ a,b,c,d      ║
╚══════════════╝

edited Feb 10, 2017 at 13:47

answered Feb 10, 2017 at 13:08

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

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

10 Comments

soandos Over a year ago

I don't seem to be getting the comma in between the values. Is there a way to make that passed in?

Gordon Linoff Over a year ago

@soandos . . . You could have the function default to using a comma, but you can't implement the separator syntax in Postgres.

soandos Over a year ago

@GordonLinoff how can I have it default to comma then?

Abelisto Over a year ago

@soandos You did not mention about comma. In this case you should to define yet another function. Answer updated.

Abelisto Over a year ago

@soandos PS Define the function as stable to make it faster.

|

Collectives™ on Stack Overflow

Postgres aggregate alias for group_concat using string_agg

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related