Introduction to PostgreSQL STRING_AGG() function

The PostgreSQLSTRING_AGG() function is an aggregate function that concatenates a list of strings and places a separator between them. It does not add the separator at the end of the string.

The following shows the syntax of theSTRING_AGG() function:

STRING_AGG (expression, separator [order_by_clause] )

TheSTRING_AGG() function accepts two arguments and an optionalORDER BY clause.

• expression is any valid expression that can resolve to a character string. If you use other types than character string type, you need to explicitlycast these values of that type to the character string type.
• separator is the separator for concatenated strings.

Theorder_by_clause is an optional clause that specifies the order of concatenated results. It has the following form:

ORDER BY expression1 {ASC | DESC}, [...]

TheSTRING_AGG() is similar to theARRAY_AGG() function except for the return type. The return value of theSTRING_AGG() function is a string whereas the return value of theARRAY_AGG() function is anarray.

Like other aggregate functions such asAVG(),COUNT(),MAX(),MIN(), andSUM(), theSTRING_AGG() function is often used with theGROUP BY clause.

PostgreSQL STRING_AGG() function examples

We will use thefilm,film_actor, andactor tables from thesample database for the demonstration:

1) Using PostgreSQL STRING_AGG() function to generate a list of comma-separated values

This example uses theSTRING_AGG() function to return a list of actor’s names for each film from thefilm table:

SELECT    f.title,    STRING_AGG (a.first_name|| ' ' || a.last_name,        ','       ORDER BY        a.first_name,        a.last_name    ) actorsFROM    film fINNER JOIN film_actor faUSING (film_id)INNER JOIN actor aUSING (actor_id)GROUP BY    f.title;

Here is the partial output:

title            |                                                                                                   actors-----------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Academy Dinosaur            | Christian Gable,Johnny Cage,Lucille Tracy,Mary Keitel,Mena Temple,Oprah Kilmer,Penelope Guiness,Rock Dukakis,Sandra Peck,Warren Nolte Ace Goldfinger              | Bob Fawcett,Chris Depp,Minnie Zellweger,Sean Guiness Adaptation Holes            | Bob Fawcett,Cameron Streep,Julianne Dench,Nick Wahlberg,Ray Johansson...

2) Using the PostgreSQL STRING_AGG() function to generate a list of emails

The following example uses theSTRING_AGG() function to build an email list for each country, with emails separated by semicolons:

SELECT    country,    STRING_AGG (email,';') email_listFROM    customerINNER JOIN address USING (address_id)INNER JOIN city USING (city_id)INNER JOIN country USING (country_id)GROUP BY    countryORDER BY    country;

The following picture shows the partial output:

Summary

• Use the PostgreSQLSTRING_AGG() function to concatenate strings and place a separator between them.

Last updated onJanuary 26, 2024

On this page

• Introduction to PostgreSQL STRING_AGG() function
• PostgreSQL STRING_AGG() function examples
• Summary