PostgreSQL – STRING_AGG() Function

The STRING_AGG() function in PostgreSQL is an aggregate function that is used to concatenate a list of strings and place a separator between them.

Syntax: STRING_AGG ( expression, separator [order_by_clause] )

Let’s analyze the above syntax. The above function accepts two arguments and an optional ORDER BY clause.

• expression is any valid expression that can resolve to a character string.
• separator is the separator for concatenated strings.

Now let’s look into some examples.For examples we will be using the sample database (ie, dvdrental).

Example 1:
We will query for a list of actor’s names for each film from the film table using the below command:

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;

Output:

Example 2:
In this example we will an email list for each country from the country table. The email in each list separated by a semi-colon.To do so we use the below command:

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;

Output:

My Personal Notes arrow_drop_up

Save