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:

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:

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.
Similar Reads
PostgreSQL String Functions PostgreSQL is a powerful, open-source relational database management system that offers a rich set of functions and operators for working with string data. String manipulation is an essential task in many applications, and PostgreSQL provides a variety of built-in functions to make working with text
8 min read
PostgreSQL - Substring Function PostgreSQL is a powerful relational database management system with extensive text processing functions, including the flexible SUBSTRING function. This function enables users to extract specific portions of a string, making it essential for text manipulation, especially when dealing with large data
4 min read
PostgreSQL - TRIM Function The TRIM() function in PostgreSQL is an essential tool for removing unwanted characters from strings. Whether we're working with user inputs, formatting text, or performing data cleansing operations, TRIM() is an invaluable function for managing string data. This article will provide an in-depth loo
4 min read
PostgreSQL - RIGHT Function The PostgreSQL RIGHT() function, allows you to extract a specified number of characters from the right side of a string. This function can be incredibly useful for various text-processing tasks.Let us get a better understanding of the RIGHT Function in PostgreSQL from this article.SyntaxRIGHT(string
2 min read
PostgreSQL - AVG() Function In PostgreSQL, the AVG() function is a powerful tool used to calculate the average value of a set of numeric values. It is one of the most frequently used aggregate functions in PostgreSQL, making it a crucial part of any database user's toolkit. This function allows users to efficiently compute the
2 min read