How to change MySQL GROUP_CONCAT to PostgreSQL string_agg?

Question

I'm migrating some MySQL code to Postgres and having a heck of a time converting the following line:

GROUP_CONCAT(
    DISTINCT id,':',foo,':',bar ORDER BY id
) as id,

This results in a comma separated list of strings like:

id:foo:bar,id2:foo2:bar2

The 'DISTINCT' is there to avoid duplicates.

I've read the equivalent of GROUP_CONCAT in Postgres is string_agg, but I can't figure out how to make it work the same way.

Edit: I may have almost answered my own question. I came up with the solution of using CONCAT. The problem with this is that now sorting is done by the concatenated string rather than by the id:

string_agg(DISTINCT CONCAT(
    id::text, ':', foo, ':', bar, ':'
), ',') as id

If I try to add 'ORDER BY id' I get an error.

Rams · Accepted Answer · 2017-08-09 05:48:25Z

1

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

edited Aug 9, 2017 at 5:48

answered Aug 9, 2017 at 5:34

Rams

2,1591 gold badge15 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to change MySQL GROUP_CONCAT to PostgreSQL string_agg?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related