VOOZH about

URL: https://www.geeksforgeeks.org/postgresql/postgresql-string_agg-function/

⇱ PostgreSQL - STRING_AGG() Function - GeeksforGeeks

  • Courses
  • Tutorials
  • Interview Prep

PostgreSQL - STRING_AGG() Function

Last Updated : 15 Jul, 2025

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
INNERJOIN 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)
GROUPBY
 country
ORDERBY
 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.


Comment

Explore