How to Concatenate Strings of a String Field in a Postgresql 'Group By' Query

How to concatenate strings of a string field in a PostgreSQL 'group by' query?

PostgreSQL 9.0 or later:

Modern Postgres (since 2010) has the string_agg(expression, delimiter) function which will do exactly what the asker was looking for:

SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;

Postgres 9 also added the ability to specify an ORDER BY clause in any aggregate expression; otherwise you have to order all your results or deal with an undefined order. So you can now write:

SELECT company_id, string_agg(employee, ', ' ORDER BY employee)
FROM mytable
GROUP BY company_id;

PostgreSQL 8.4.x:

PostgreSQL 8.4 (in 2009) introduced the aggregate function array_agg(expression) which collects the values in an array. Then array_to_string() can be used to give the desired result:

SELECT company_id, array_to_string(array_agg(employee), ', ')
FROM mytable
GROUP BY company_id;

PostgreSQL 8.3.x and older:

When this question was originally posed, there was no built-in aggregate function to concatenate strings. The simplest custom implementation (suggested by Vajda Gabo in this mailing list post, among many others) is to use the built-in textcat function (which lies behind the || operator):

CREATE AGGREGATE textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);

Here is the CREATE AGGREGATE documentation.

This simply glues all the strings together, with no separator. In order to get a ", " inserted in between them without having it at the end, you might want to make your own concatenation function and substitute it for the "textcat" above. Here is one I put together and tested on 8.3.12:

CREATE FUNCTION commacat(acc text, instr text) RETURNS text AS $$
  BEGIN
    IF acc IS NULL OR acc = '' THEN
      RETURN instr;
    ELSE
      RETURN acc || ', ' || instr;
    END IF;
  END;
$$ LANGUAGE plpgsql;

This version will output a comma even if the value in the row is null or empty, so you get output like this:

a, b, c, , e, , g

If you would prefer to remove extra commas to output this:

a, b, c, e, g

Then add an ELSIF check to the function like this:

CREATE FUNCTION commacat_ignore_nulls(acc text, instr text) RETURNS text AS $$
  BEGIN
    IF acc IS NULL OR acc = '' THEN
      RETURN instr;
    ELSIF instr IS NULL OR instr = '' THEN
      RETURN acc;
    ELSE
      RETURN acc || ', ' || instr;
    END IF;
  END;
$$ LANGUAGE plpgsql;

Postgresql GROUP_CONCAT equivalent?

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).

Concatenate multiple result rows of one column into one, group by another column

Simpler with the aggregate function string_agg() (Postgres 9.0 or later):

SELECT movie, string_agg(actor, ', ') AS actor_list
FROM   tbl
GROUP  BY 1;

The 1 in GROUP BY 1 is a positional reference and a shortcut for GROUP BY movie in this case.

string_agg() expects data type text as input. Other types need to be cast explicitly (actor::text) - unless an implicit cast to text is defined - which is the case for all other string types (varchar, character, name, ...) and some other types.

As isapir commented, you can add an ORDER BY clause in the aggregate call to get a sorted list - should you need that. Like:

SELECT movie, string_agg(actor, ', ' ORDER BY actor) AS actor_list
FROM   tbl
GROUP  BY 1;

But it's typically faster to sort rows in a subquery. See:

• Create array in SELECT

PostgreSQL GROUP BY clause

You could use the rank window function to rank the employees per city, and then wrap that query with another query to get only the highest one per city:

SELECT ename, city, salary
FROM   (SELECT ename, city, salary,
               RANK() OVER (PARTITION BY city ORDER BY salary DESC) AS rk
        FROM   employee) t
WHERE  rk = 1

Concatenate string instead of just replacing it

Storing comma separated values is a huge mistake to begin with. But if you really want to make your life harder than it needs to be, you might want to create a function that merges two comma separated lists:

create function merge_lists(p_one text, p_two text)
  returns text
as
$$
  select string_agg(item, ',')
  from (
    select e.item
    from unnest(string_to_array(p_one, ',')) as e(item)
    where e.item <> '' --< necessary because of the leading , in your data
    union 
    select t.item
    from unnest(string_to_array(p_two, ',')) t(item)
    where t.item <> ''
  ) t;
$$
language sql;  

If you are using Postgres 14 or later, unnest(string_to_array(..., ',')) can be replace with string_to_table(..., ',')

Then your INSERT statement gets a bit simpler:

INSERT  INTO t00 (userid,a,b)  VALUES (0,1,',fish,eagle')
  ON CONFLICT (userid)
  DO UPDATE SET
    a = EXCLUDED.a,
    b = merge_lists(excluded.b, t00.b);

Postgresql how to concatenate strings but only when values are different?

Just add distinct inside string_agg:

string_agg(distinct country, ', ')

Concatenate distinct values in a group by

What Laurenz meant with string_agg() is the following

SELECT
  group, 
  STRING_AGG(Provider,',') as ProviderList
FROM data 
GROUP BY 1

Optionally you could also use:

STRING_AGG(provider,',' order by Provider)

Concatenate rows into a text string

Use String_agg function

SELECT row1,
       row2, 
       string_agg(row3, ',') as row3
FROM your_table
GROUP BY row1,
         row2

---

Related Topics