STRING_AGG
MySQL equivalent:
Joins values from a group of rows into one string with a delimiter.
PostgreSQL 17.5
STRING_AGG([DISTINCT] expression, delimiter [ORDER BY ...])Parameters
- DISTINCTkeywordoptional
- Join only unique values
- expressiontext
- Text expression whose values are joined; a number must be cast to text
- delimitertext
- Delimiter between values
- ORDER BYoptional
- Order of values in the string, written after delimiter without a comma
Return value
A string (text); NULLs are skipped, NULL if all values are NULL
Examples
All names separated by commas
PostgreSQL 17.5
SELECT STRING_AGG(member_name, ', ')
FROM FamilyMembers;Names by status in alphabetical order
PostgreSQL 17.5
SELECT status,
STRING_AGG(
member_name,
', '
ORDER BY member_name
) AS members
FROM FamilyMembers
GROUP BY status
ORDER BY status;Numbers via ::text and unique statuses
PostgreSQL 17.5
SELECT STRING_AGG(
member_id::text,
','
ORDER BY member_id
) AS ids,
STRING_AGG(DISTINCT status, ', ') AS statuses
FROM FamilyMembers;Details
The values must be text: STRING_AGG(member_id, ',') causes an error, while STRING_AGG(member_id::text, ',') works.
Without ORDER BY the order of values is not guaranteed and can change from run to run. Together with DISTINCT, ORDER BY can only use expression itself.