MySQL group concat conversion to Postgres using two concatenated columns

Question

How can I create a similar query using Postgres to recreate this MySQL version.

SELECT u.id, GROUP_CONCAT(CONCAT(r.firstname,' ',r.lastname, ' [', r.type,']') SEPARATOR ', ') AS names
FROM reference r, users u
WHERE r.user_id = u.id
GROUP BY r.user_id

mu is too short · Accepted Answer · 2017-01-28 18:01:57Z

3

Use string_agg. Also, use explicit join syntax.

SELECT u.id, string_agg(r.firstname || ' ' || r.lastname || ' [' || r.type || ']' , ', ') AS names
FROM reference r join users u
on r.user_id = u.id
GROUP BY r.user_id

edited Jan 28, 2017 at 18:01

mu is too short

436k71 gold badges863 silver badges822 bronze badges

answered Jan 28, 2017 at 16:58

Gurwinder Singh

39.7k6 gold badges62 silver badges87 bronze badges

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

1 Comment

user469626 Over a year ago

This worked perfect, thanks! Been struggling with this for a bit so much appreciated.

Collectives™ on Stack Overflow

MySQL group concat conversion to Postgres using two concatenated columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related