PostgreSQL – STRING_AGG() Function
Last Updated :
01 Jun, 2020
The STRING_AGG() function in PostgreSQL is an aggregate function that is used to concatenate a list of strings and place a separator between them.
Syntax: STRING_AGG ( expression, separator [order_by_clause] )
Let’s analyze the above syntax. The above function accepts two arguments and an optional ORDER BY clause.
expression
is any valid expression that can resolve to a character string.
separator
is the separator for concatenated strings.
Now let’s look into some examples.For examples we will be using the sample database (ie, dvdrental).
Example 1:
We will query for a list of actor’s names for each film from the film
table using the below command:
SELECT
f.title,
STRING_AGG (
a.first_name || ' ' || a.last_name,
', '
ORDER BY
a.first_name,
a.last_name
) 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 2:
In this example we will an email list for each country from the country
table. The email in each list separated by a semi-colon.To do so we use the below command:
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:
Share your thoughts in the comments
Please Login to comment...