Postgresql GROUP_CONCAT equivalent?

postgres string_agg integer
postgres json_agg
postgresql array_agg(distinct)
postgres concat
redshift group_concat
postgres arbitrary
postgresql mode
postgresql bool or

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?

Postgres GROUP_CONCAT (Example), You can achieve the same thing in Postgres using string_agg. CREATE TABLE animal ( id serial primary key, farm_id integer, name varchar );  Tap Into Your PostgreSQL Data to Get Insights Quickly. Try Free!

Since 9.0 this is even easier:

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

MySQL's group_concat Equivalents in PostgreSQL , group_concat in MySQL. MySQL has a very handy function which concatenates strings from a group into one string. For example, let's take a  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.

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

Will do as well.

GROUP_CONCAT in PostgreSQL without aggregate functions at , I described an aggregate function to concatenate strings in PostgreSQL, similar to GROUP_CONCAT in MySQL . It's very useful if you have a  Postgres GROUP_CONCAT equivalent? [duplicate] Ask Question Asked 4 years, 7 months ago. Postgresql GROUP_CONCAT equivalent? Related. 334.

Try like this:

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

Documentation: 9.5: Aggregate Functions, bool, bool, equivalent to bool_and. json_agg(expression), any, json, aggregates values, including nulls, as a JSON array. jsonb_agg(expression), any​, jsonb  The SQL Server Equivalent to GROUP_CONCAT() Ian on May 30, 2018 February 14, 2020 Before SQL Server 2017 came along, there wasn’t a T-SQL equivalent of the MySQL GROUP_CONCAT() function. This function allows you to return a result set as a comma-separated list, as opposed to listing each row as a separate row (as with a normal result set).

and the version to work on the array type:

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

Concatenating Rows in Redshift, Postgres, & MySQL, There's group_concat in MySQL, string_agg in Postgres, and listagg in redshift. Let's first look at how to use each of these functions. Then, we'll  There is a string_agg() builtin which does what you want, but you specifically ask for it to be named group_concat for MySQL compatibility. Unfortunately, string_agg() uses an internal data type for accumulation (presumably to avoid copying the whole buffer on each append, I have not looked at the source though) and I didn't find a way to declare a SQL aggrerate identical to string_agg().

Postgresql GROUP_CONCAT equivalent?, function GROUP_CONCAT , but that doesn't seem to work here Is there an equivalent for PostgreSQL, or another way to accomplish this? select name, group_concat(cast(seq_id as char)) as id_of_duplicates from produk group by name order by name; PostgreSQL: create aggregate array_accum (sfunc = array_append, basetype = anyelement, stype = anyarray, initcond = '{}'); CREATE OR REPLACE FUNCTION _group_concat(text, text) RETURNS text AS $$ SELECT CASE WHEN $2 IS NULL THEN $1 WHEN $1 IS NULL THEN $2

group_concat as an alternative for array_agg – Adrian Serafin, group_concat as an alternative for array_agg basis and I often find myself searching for MySQL equivalent for some PostgreSQL features. In MySQL, you can aggregate columns into a single value using GROUP_CONCAT. You can achieve the same thing in Postgres using string_agg. CREATE TABLE animal ( id serial primary key, farm_id integer, name varchar ); INSERT INTO animal (farm_id, name) VALUES (1, 'cow'), (1, 'horse'); CREATE TABLE tool ( id serial primary key, farm_id integer, name varchar ); INSERT INTO tool (farm_id, name

String Aggregation in PostgreSQL, SQL Server, and MySQL , This is the PostgreSQL 8.4+ equivalent to the SQL Server Window CTE function for as far back as MySQL 3.23 days GROUP_CONCAT. group_concat as an alternative for array_agg For quite some time I have been hardcore PostgreSQL user. I like to leverage database capabilities when possible to get faster response times in my Ruby On Rails applications.

Comments
  • Not an answer, but check out postgresonline.com/journal/index.php?/archives/….
  • stackoverflow.com/questions/1943433/…
  • possible duplicate of Simulating group_concat MySQL function in SQL Server?
  • I think the best answer is still in another question: stackoverflow.com/a/47638417/243233
  • And in 9.0 you will have listagg()
  • 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
  • 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.
  • FYI: here's a link to docs on array_agg in 8.4
  • 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)
  • It's awesome that distinct works with string_agg, so one can use string_agg(distinct some_solumn, ',')