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.