PostgreSQL – STRING_AGG() Function

Last Updated : 05 Jul, 2024

The STRING_AGG() function in PostgreSQL is a powerful aggregate function used to concatenate a list of strings with a specified separator. This function is essential for combining string values from multiple rows into a single string, making data aggregation more efficient and readable.

Let us get a better understanding of the syntax, usage, and practical examples of the STRING_AGG() function in PostgreSQL, using the sample database dvdrental.

Syntax

STRING_AGG ( expression, separator [ORDER BY clause] )

Parameters:

Expression: Any valid expression that can resolve to a character string. This is the string to be concatenated.
Separator: A string that will be placed between each concatenated expression.
ORDER BY Clause (Optional): Defines the order in which the strings should be concatenated.

Use Cases for STRING_AGG()

Reporting: Generate concatenated lists for reports, such as combining product names, customer emails, or employee roles.
Data Export: Create formatted strings for exporting data to CSV or other text-based formats.
Data Visualization: Prepare data for visualization tools that require concatenated string inputs.

PostgreSQL STRING_AGG() Function Examples

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

Example 1: Listing Actor Names for Each Film

We want to generate a list of actor names for each film, separated by commas. Here’s the query:

Query:

SELECT
    f.title,
    STRING_AGG (
    a.first_name || ' ' || a.last_name,
        ', '
       ORDER BY
        a.first_name,
        a.last_name
    ) AS 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 1: Output

Explanation: This query concatenates actor names for each film, separated by commas and ordered alphabetically by first name and last name.

Example 2: Generating Email Lists by Country

In this example, we will create an email list for each country, with emails separated by a semicolon. Here’s the query:

Query:

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:

Example 2: Output

Explanation: This query generates a semicolon-separated list of emails for each country, ordered by country name.

Important Points About STRING_AGG() Function in PostgreSQL

The PostgreSQL STRING_AGG() function is used to concatenate a list of strings into a single string with a specified separator.

STRING_AGG() ignores NULL values in the concatenation process, ensuring that only non-NULL strings are included in the final result.

Commonly used with GROUP BY to aggregate data and produce concatenated string results for grouped rows.

STRING_AGG() Function can be combined with other PostgreSQL functions like COALESCE to handle NULL values or FORMAT to format the output string.