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
PostgreSQL - Function Returning A Table In PostgreSQL, the ability to create functions that return tables enhances the power and flexibility of our database operations. These PostgreSQL RETURN TABLE functions allow us to execute complex queries and return structured data efficiently. In this article, we will explain the PostgreSQL functio
4 min read
PostgreSQL - CONCAT_WS Function In PostgreSQL, the CONCAT_WS function is a powerful and versatile tool for concatenating strings with a specified separator. This function not only combines multiple string values but also efficiently handles NULL values, ignoring them in the concatenation process. In this article, we will go deep i
3 min read
PostgreSQL - ARRAY_AGG() Function The PostgreSQL ARRAY_AGG function is a aggregate function that allows users to combine values from multiple rows into an array. This function is particularly useful when managing grouped data and returning multiple values in a single row. In this article, We will learn about the What is PostgreSQL A
4 min read
PostgreSQL - ASCII Function When working with PostgreSQL, you might need to derive the ASCII (American Standard Code for Information Interchange) code of a character. The PostgreSQL ASCII() function is a handy tool for this purpose. In the case of UTF-8 encoding, the ASCII() function returns the Unicode code point of the chara
2 min read
PostgreSQL - UPPER Function In PostgreSQL, the UPPER function is utilized to convert a string into uppercase. This function is handy when you need to standardize text data by converting it to a uniform case, especially for comparison or display purposes.Let us get a better understanding of the UPPER Function in PostgreSQL from
2 min read