Postgresql Group_Concat Equivalent

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

Can we define a GROUP_CONCAT function in PostgreSQL?

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

Defining group_concat() function would not work either, as pg has to be made aware that it is an aggregate, not a function with an aggregate hidden inside, which would not work. Such a function would operate on one row at a time: any aggregate inside would just aggregate a single row and return it unchanged...

Thus, this code will accumulate the elements into an array, then add the "," delimiters with array_to_string. I will use the array_agg() declaration (before it became a built-in) as a model, and simply add a finalizer function which will convert the aggregated array into text.

CREATE OR REPLACE FUNCTION _group_concat_finalize(anyarray)
RETURNS text AS $$
    SELECT array_to_string($1,',')
$$ IMMUTABLE LANGUAGE SQL;

CREATE AGGREGATE group_concat(anyelement) (
   SFUNC=array_append,
   STYPE=anyarray,
   FFUNC=_group_concat_finalize,
   INITCOND='{}'
);

SELECT group_concat(x) FROM foo;

The nice thing is that it should work fine for any type, without hassle, thanks to the generic types "anyarray" and "anyelement".

I would presume this would be slower than string_agg() if string_agg does indeed avoid to copy the whole aggregation array on each append. This should matter only if the number of rows to be grouped into each set is large, though. In this case, you probably can spend a minute editing the SQL query ;)

http://sqlfiddle.com/#!17/c452d/1

PostgreSQL group_concat rows as json

You will have to use JSON_BUILD_OBJECT to build the individual JSON objects and then JSON_AGG to build an array of those objects:

SELECT cat.id, cat.category,
(SELECT json_agg(
  json_build_object(
    'rate',mod.rate,
    'model_name', mod.modelName, 
    'capacity',mod.capacity
  )) AS vehicles 
  FROM models AS mod WHERE category_id = cat.id
) AS models
FROM categories AS cat 

GROUP_CONCAT for similar records in PostgreSQL

It is a bit hard trying out a query without table definitions or an SQL fiddle, but :

You can GROUP BY both cliente and email and then use array_agg, which will return an ARRAY. You can use string_agg(sale_order.name, ' & ') if you want really want a string, but arrays are usually easier to work with.

SELECT array_agg(sale_order.name) AS orden,
       split_part(res_partner.name,' ',1) AS cliente,
       res_partner.email
FROM  sale_order,product_pricelist,res_currency,res_partner
WHERE sale_order.partner_id = res_partner.id 
  AND sale_order.pricelist_id = product_pricelist.id
  AND product_pricelist.currency_id = res_currency.id 
  AND sale_order.state = 'sent'
GROUP BY cliente, email;

Group_concat equivalent in postgresql 8.2.11

The "not quite duplicate" in the comments should point you in the right direction: create your own aggregate function. First you'll need a non-aggregate string concatenation function, something like this:

create function concat(t1 text, t2 text) returns text as $$
begin
    return t1 || t2;
end;
$$ language plpgsql;

Then you can define your own aggregate version of that function:

create aggregate group_concat(
    sfunc    = concat,
    basetype = text,
    stype    = text,
    initcond = ''
);

Now you can group_concat all you want:

select group_concat(s)
from t
group by g

I dug this out of my archives but I think it should work in 8.2.

Keep in mind that 8.2 is no longer supported so you might want to upgrade to at least 8.4 as soon as possible.

How to change MySQL GROUP_CONCAT to PostgreSQL string_agg?

You can do something like below.

select 
string_agg(DISTINCT CONCAT(
    id::text, ':', foo, ':', bar, ':'
), ',') as id from (select * from table t order by id) as al

---

Related Topics