0

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.

1 Answer 1

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 
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.