Postgresql GROUP_CONCAT equivalent?

Question

I have a table and I'd like to pull one row per id with field values concatenated.

In my table, for example, I have this:

TM67 | 4  | 32556
TM67 | 9  | 98200
TM67 | 72 | 22300
TM99 | 2  | 23009
TM99 | 3  | 11200

And I'd like to output:

TM67 | 4,9,72 | 32556,98200,22300
TM99 | 2,3    | 23009,11200

In MySQL I was able to use the aggregate function GROUP_CONCAT, but that doesn't seem to work here... Is there an equivalent for PostgreSQL, or another way to accomplish this?

Not an answer, but check out http://www.postgresonline.com/journal/index.php?/archives/14-CrossTab-Queries-in-PostgreSQL-using-tablefunc-contrib.html. — Kuberchaun, Apr 01 '10 at 14:21
http://stackoverflow.com/questions/1943433/postgresql-concat-ws-like-function — Milen A. Radev, Apr 01 '10 at 15:41
possible duplicate of [Simulating group\_concat MySQL function in SQL Server?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) — ntalbs, Feb 10 '15 at 03:49
I think the best answer is still in another question: https://stackoverflow.com/a/47638417/243233 — Jus12, Dec 05 '17 at 14:27

score 390 · Answer 1 · edited Apr 06 '12 at 20:18

390

Since 9.0 this is even easier:

SELECT id, 
       string_agg(some_column, ',')
FROM the_table
GROUP BY id

edited Apr 06 '12 at 20:18

Lukas Eder

211,314
129
689
1,509

answered Jan 10 '12 at 12:51

57

Note that the syntax also allows you to specify the order of values in the string (or array, using `array_agg`) e.g. `string_agg(some_column, ',' ORDER BY some_column)` or even `string_agg(surname || ', ' || forename, '; ' ORDER BY surname, forename)` – IMSoP May 16 '13 at 14:33
21

It's awesome that `distinct` works with string_agg, so one can use `string_agg(distinct some_solumn, ',')` – arun Jul 04 '18 at 00:33
12

Note that you may need to cast the column value to `TEXT` if it is a non-stringable value (ie. `uuid`). This would look like `string_agg(some_column::text, ',')` – Kendall Sep 11 '19 at 16:56
1

If column type is integer, don't forget to convert or use concat(column, '') for implicit conversion – Takman Oct 01 '20 at 14:48
1

PS, string_agg(value, ',') can be use only if the `value` is string. For example, if the `value` is integer `value` need to be casted to string like `value::character varying` – ZenithS Nov 25 '20 at 07:19
hello, I need some help when I do that, of course its the same result, example: "60-70,60-70,60-70,60-70" in the same column, but how can I that only appear once? "60-70" I used string_agg – Mau España Aug 19 '21 at 14:48

score 313 · Accepted Answer · edited Sep 14 '17 at 20:37

This is probably a good starting point (version 8.4+ only):

SELECT id_field, array_agg(value_field1), array_agg(value_field2)
FROM data_table
GROUP BY id_field

array_agg returns an array, but you can CAST that to text and edit as needed (see clarifications, below).

Prior to version 8.4, you have to define it yourself prior to use:

CREATE AGGREGATE array_agg (anyelement)
(
    sfunc = array_append,
    stype = anyarray,
    initcond = '{}'
);

(paraphrased from the PostgreSQL documentation)

Clarifications:

The result of casting an array to text is that the resulting string starts and ends with curly braces. Those braces need to be removed by some method, if they are not desired.
Casting ANYARRAY to TEXT best simulates CSV output as elements that contain embedded commas are double-quoted in the output in standard CSV style. Neither array_to_string() or string_agg() (the "group_concat" function added in 9.1) quote strings with embedded commas, resulting in an incorrect number of elements in the resulting list.
The new 9.1 string_agg() function does NOT cast the inner results to TEXT first. So "string_agg(value_field)" would generate an error if value_field is an integer. "string_agg(value_field::text)" would be required. The array_agg() method requires only one cast after the aggregation (rather than a cast per value).

To get CSV the query should be: SELECT id_field, array_to_string(array_agg(value_field1), ','), array_to_string(array_agg(value_field2),', ') FROM data_table GROUP BY id_field — Nux, Feb 16 '12 at 16:08
You can't use array_to_string in all cases here. If your value_field contains an embedded comma, the resulting CSV is incorrect. Using array_agg() and casting to TEXT properly quotes strings with embedded commas. The only caveat is that it also includes the starting and ending curly braces, hence my statement "and edit as needed". I will edit to clarify that point. — Matthew Wood, Feb 21 '12 at 20:00
FYI: here's a link to [docs on array_agg in 8.4](http://www.postgresql.org/docs/8.4/static/functions-aggregate.html) — Michael Rusch, Apr 22 '14 at 21:35

score 60 · Answer 3 · edited Feb 25 '11 at 23:46

60

SELECT array_to_string(array(SELECT a FROM b),', ');

Will do as well.

edited Feb 25 '11 at 23:46

Kornel

97,764
37
219
309

answered Dec 17 '10 at 09:07

genobis

1,081
9
13

Is it possible to do something like in [this comment](http://stackoverflow.com/questions/2560946/postgresql-group-concat-equivalent#comment23843695_8803563), where you aggregate in a certain order? How would you handle grouping by one column and ordering by another one (for example, to concatenate variables within a longitudinal data set)? – Michael A Jan 08 '15 at 18:22

score 31 · Answer 4 · answered Jan 11 '14 at 14:36

31

Try like this:

select field1, array_to_string(array_agg(field2), ',')
from table1
group by field1;

answered Jan 11 '14 at 14:36

max_spy

654
10
13

score 6 · Answer 5 · answered Oct 08 '21 at 19:36

Assuming that the table your_table has three columns (name, id, value), the query is this one:

  select name, 
         array_to_string(array_agg(id), ','), 
         array_to_string(array_agg(value), ',')
    from your_table
group by name
order by name
;

"TM67"  "4,9,72"    "32556,98200,22300"
"TM99"  "2,3"       "23009,11200"

KI

Sławomir Lenart · Answer 6 · 2019-02-06T13:51:30.700

3

and the version to work on the array type:

select
  array_to_string(
    array(select distinct unnest(zip_codes) from table),
    ', '
);

edited Feb 06 '19 at 13:51

answered Jan 30 '19 at 10:58

Sławomir Lenart

7,543
4
45
61

Duplicate answer, @max_spy said the same thing five years ago – Emil Vikström Feb 06 '19 at 13:12
@EmilVikström: you have right to be wrong, but read carefully. It's not only different, but I gave an example, which works with array type - like zip_codes being `character varying(5)[]`. Also, I've verified that for my purpose - unnest is needed, otherwise you will see `ERROR: cannot accumulate arrays of different dimensionality`. – Sławomir Lenart Feb 06 '19 at 13:47
@Sław It would be cleaner (on this page) for you to eduacationally explain the uniqueness, intent, and purpose of your answer by editing your answer (not explaining via comment). – mickmackusa Aug 22 '23 at 05:06

score 2 · Answer 7 · answered Dec 05 '22 at 14:57

2

In my experience, I had bigint as column type. So The below code worked for me. I am using PostgreSQL 12.

Type cast is happening here. (::text).

string_agg(some_column::text, ',')

answered Dec 05 '22 at 14:57

Kumaresan Perumal

1,926
2
29
35

score 1 · Answer 8 · edited Oct 31 '18 at 18:56

1

My sugestion in postgresql

SELECT cpf || ';' || nome || ';' || telefone  
FROM (
      SELECT cpf
            ,nome
            ,STRING_AGG(CONCAT_WS( ';' , DDD_1, TELEFONE_1),';') AS telefone 
      FROM (
            SELECT DISTINCT * 
            FROM temp_bd 
            ORDER BY cpf DESC ) AS y
      GROUP BY 1,2 ) AS x

edited Oct 31 '18 at 18:56

mypetlion

2,415
5
18
22

answered Oct 31 '18 at 18:16

Lucas Cabral

11
1

1

Why are you doing `ORDER BY` in an inner query? Won't the ordering get lost anyway? – mypetlion Oct 31 '18 at 18:35

score -2 · Answer 9 · edited Dec 22 '19 at 19:41

-2

Hope below Oracle query will work.

Select First_column,LISTAGG(second_column,',') 
    WITHIN GROUP (ORDER BY second_column) as Sec_column, 
    LISTAGG(third_column,',') 
    WITHIN GROUP (ORDER BY second_column) as thrd_column 
FROM tablename 
GROUP BY first_column

edited Dec 22 '19 at 19:41

Chris Catignani

5,040
16
42
49

answered Dec 22 '19 at 15:12

kiruba

129
5

I tested it on https://rextester.com/l/postgresql_online_compiler and didn't work: 42883: function listagg(text, unknown, text) does not exist – Manuel Romeiro Feb 04 '20 at 12:07
2

Oracle has different syntax and functions than postgres. – Herman J. Radtke III Feb 05 '20 at 16:37

Postgresql GROUP_CONCAT equivalent?

9 Answers9

Linked

Related