STRING_AGG

MySQL equivalent:

Joins values from a group of rows into one string with a delimiter.

PostgreSQL 17.9

STRING_AGG([DISTINCT] expression, delimiter [ORDER BY sort_expression [ASC | DESC] [NULLS FIRST | NULLS LAST]])

DISTINCT: Join only unique values
KeywordOptional
expression: Text expression whose values are joined; a number must be cast to text
text
delimiter: Delimiter between values
text
ORDER BY sort_expression [ASC | DESC] [NULLS FIRST | NULLS LAST]: Order of values, arbitrary without it
Optional
sort_expression: Column or expression to sort by
ASC | DESC: Direction, ASC by default
KeywordOptional
ASCDESC
NULLS FIRST | NULLS LAST: Where NULLs go, by default last with ASC and first with DESC
KeywordOptional
NULLS FIRSTNULLS LAST

Return value

A string (text); NULLs are skipped, NULL if all values are NULL

PostgreSQL 17.9

SELECT STRING_AGG(member_name, ', ')
FROM FamilyMembers;

string_agg
Headley Quincey, Flavia Quincey, Andie Quincey, Lela Quincey, Annie Quincey, Ernest Forrest, Constance Forrest, Wednesday Addams

PostgreSQL 17.9

SELECT status,
	STRING_AGG(
		member_name,
		', '
		ORDER BY member_name
	) AS members
FROM FamilyMembers
GROUP BY status
ORDER BY status;

status	members
daughter	Annie Quincey, Lela Quincey, Wednesday Addams
father	Ernest Forrest, Headley Quincey
mother	Constance Forrest, Flavia Quincey
son	Andie Quincey

PostgreSQL 17.9

SELECT STRING_AGG(
		member_id::text,
		','
		ORDER BY member_id
	) AS ids,
	STRING_AGG(DISTINCT status, ', ') AS statuses
FROM FamilyMembers;

ids	statuses
1,2,3,4,5,6,7,8	daughter, father, mother, son

The values must be text: STRING_AGG(member_id, ',') causes an error, while STRING_AGG(member_id::text, ',') works.

Together with DISTINCT, sort_expression must match expression.