PostgreSQL Tutorial: STRING_AGG Function

Summary: In this tutorial, you will learn how to use the PostgreSQL STRING_AGG() function to concatenate strings and place a separator between them.

Table of Contents

Introduction to PostgreSQL STRING_AGG() function

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

The following shows the syntax of the STRING_AGG() function:

STRING_AGG ( expression, separator [order_by_clause] )

The STRING_AGG() function accepts two arguments and an optional ORDER 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 explicitly cast these values of that type to the character string type.
• separator is the separator for concatenated strings.

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

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

The STRING_AGG() is similar to the ARRAY_AGG() function except for the return type. The return type of the STRING_AGG() function is the string while the return type of the ARRAY_AGG() function is the array.

Like other aggregate functions such as AVG(), COUNT(), MAX(), MIN(), and SUM(), the STRING_AGG() function is often used with the GROUP BY clause.

PostgreSQL STRING_AGG() function examples

We will use the film, film_actor, and actor tables from the sample database for the demonstration.

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

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

SELECT
    f.title,
    STRING_AGG (
	a.first_name || ' ' || a.last_name,
        ','
       ORDER BY
        a.first_name,
        a.last_name
    ) actors
FROM
    film f
INNER JOIN film_actor fa USING (film_id)
INNER JOIN actor a USING (actor_id)
GROUP BY
    f.title;

Here is the partial output:

B) Using STRING_AGG() function to generate a list of emails

The following example uses the STRING_AGG() function to build an email list for each country. The email in each list separated by a semi-colon.

SELECT
    country,
    STRING_AGG (email, ';') email_list
FROM
    customer
INNER JOIN address USING (address_id)
INNER JOIN city USING (city_id)
INNER JOIN country USING (country_id)
GROUP BY
    country
ORDER BY
    country;

The following picture shows the partial output:

In this tutorial, you have learned how to use the PostgreSQL STRING_AGG() function to concatenate strings and place a separator between them.

See more

PostgreSQL Tutorial: Aggregate Functions

PostgreSQL Documentation: Aggregate Functions