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 Connection String A connection string is an essential component that enables applications to communicate with databases or other data sources by providing the necessary configuration details. It consolidates critical information such as the server address, database name, user credentials, and additional parameters li
4 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 - RANK Function The RANK() function in PostgreSQL is an advanced analytical tool used to assign a rank to each row within a specific partition in a result set. This function is especially beneficial for ranking items based on specified criteria, making it ideal for data analysis and reporting tasks like identifying
4 min read
PostgreSQL - SUM() Function The SUM() function in PostgreSQL is used to calculate the sum of values in a numeric column. This article will guide you through the syntax, important considerations, and practical examples of using the SUM() function in PostgreSQL.SyntaxSUM(column) The following points need to be kept in mind while
2 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