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"

https://www.postgresql.org/docs/current/static/sql-createfunction.html. However, I would recommend that you change the application rather than the database. — Gordon Linoff, Feb 10 '17 at 12:40
@GordonLinoff See update. Its not possible for policy reasons, though trust me, I would prefer that — soandos, Feb 10 '17 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. — , Feb 10 '17 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, Feb 10 '17 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. — , Feb 10 '17 at 13:19
@a_horse_with_no_name The database is currently H2, which has a postgres emulation mode (don't ask why) — soandos, Feb 10 '17 at 13:21

Abelisto · Accepted Answer · 2017-02-10T13:47:34.607

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 '17 at 13:47

answered Feb 10 '17 at 13:08

Abelisto

14,826
2
33
41

I don't seem to be getting the comma in between the values. Is there a way to make that passed in? – soandos Feb 10 '17 at 13:17
@soandos . . . You could have the function default to using a comma, but you can't implement the `separator` syntax in Postgres. – Gordon Linoff Feb 10 '17 at 13:34
@GordonLinoff how can I have it default to comma then? – soandos Feb 10 '17 at 13:39
@soandos You did not mention about comma. In this case you should to define yet another function. Answer updated. – Abelisto Feb 10 '17 at 13:46
@soandos PS Define the function as `stable` to make it faster. – Abelisto Feb 10 '17 at 13:50
Is it also pure, or not really? – soandos Feb 10 '17 at 14:03
@soandos Don't understand the question. – Abelisto Feb 10 '17 at 14:10
+1, I think this is as good as you're going to get it via DDL statements. Unfortunately, performance starts to collapse after a few thousand records, as each concat needs to copy the intermediate result into a brand new `text` value. `string_agg()` avoids this by using internal accumulators, but I don't think you can emulate this unless you're willing to write some server-side C code... – Nick Barnes Feb 10 '17 at 14:10
How to do it when the values are numeric and not text? – Jus12 Dec 04 '17 at 17:06
@Jus12 `cast(value as text)`? – Abelisto Dec 05 '17 at 09:50

Postgres aggregate alias for group_concat using string_agg

1 Answers1