Learn how to use the PostgreSQL STRING_AGG function to concatenate strings with a specified delimiter. Improve query results and customize output strings ⚙️
How to Use STRING_AGG() Function in PostgreSQL
The STRING_AGG() function is an aggregate function in PostgreSQL used to concatenate values from multiple rows into a single string. This handy function allows easy string concatenation directly in your SQL queries. In this article, we’ll explore the basics of using STRING_AGG() and look at some examples of it in action.
What is STRING_AGG()?
STRING_AGG() is a built-in aggregate function introduced in PostgreSQL 9.0. It takes a column or expression as input and concatenates the values into a single string.
The basic syntax of STRING_AGG() is:
SELECT STRING_AGG(column, separator) FROM table GROUP BY group_column
- column is the column or expression you want to concatenate
- separator is the delimiter string added between values
- group_column is used with GROUP BY to specify the groups to concatenate within
Some key things to note about STRING_AGG():
- It requires the GROUP BY clause like other aggregate functions
- The separator is not added after the last value
- NULL values are skipped and not included in the result
- Values can be sorted using ORDER BY in the function
Why Use STRING_AGG()?
Concatenating values from multiple rows into a single string is a common need when generating reports or analyses from database tables.
Doing this concatenation in application code can be cumbersome. STRING_AGG() provides an easy way to handle it directly in SQL.
Some examples of when STRING_AGG() is useful:
- Generating a list of comma-separated names for a report
- Combining values from related rows into a single string
- Aggregating data while retaining a string representation
- Concatenating columns for easier analysis
By handling string concatenation in the database, STRING_AGG() can save application code from doing this work.
To demonstrate STRING_AGG() in action, let’s look at some example uses with a sample database table.
First we’ll create a simple users table with some data:
CREATE TABLE users ( id INT, first_name VARCHAR(50), last_name VARCHAR(50), country VARCHAR(50) ); INSERT INTO users VALUES (1, 'John', 'Doe', 'USA'), (2, 'Jane', 'Doe', 'Canada'), (3, 'Bob', 'Smith', 'USA'), (4, 'Mary', 'Jones', 'Mexico');
This gives us a table with users from different countries.
Basic STRING_AGG() Example
To get a list of user first names by country, we can use STRING_AGG():
SELECT country, STRING_AGG(first_name, ', ') AS names FROM users GROUP BY country;
This would return:
USA, John, Bob Canada, Jane Mexico, Mary
The first names are concatenated by country with commas between them.
Sorting Concatenated Values
To sort the concatenated values, add an ORDER BY clause:
SELECT STRING_AGG(first_name, ', ' ORDER BY first_name DESC) FROM users;
Now the names are concatenated in descending order:
John, Mary, Jane, Bob
You can filter which values get concatenated using a CASE statement:
SELECT STRING_AGG(CASE WHEN country = 'USA' THEN first_name END, ', ') FROM users;
This concatenates only names where the country is ‘USA’:
As you can see, STRING_AGG() provides a flexible way to concatenate values directly in PostgreSQL without needing procedural code.
When to Avoid STRING_AGG()
While handy, STRING_AGG() isn’t ideal for every situation. Some cases where alternatives may be better:
- You need the raw concatenated string for additional string manipulation. Doing this in application code may be easier.
- Performance with very large datasets. STRING_AGG() can sometimes be slow with ample data.
- You want a concatenated value for each row. Consider FOR XML PATH instead.
So weigh the tradeoffs when deciding between STRING_AGG() and other methods.
The STRING_AGG() function is a useful tool for aggregating data into a single concatenated string value in PostgreSQL. Key points about STRING_AGG():
- Concatenates values from multiple rows into a string
- Requires GROUP BY to specify the groups
- Skips NULL values in the result
- Allows sorting concatenated values with ORDER BY
- Avoids needing to manually concatenate in application code
In this comprehensive article, we have explored the fundamental concepts of utilizing the STRING_AGG() function in PostgreSQL, providing practical examples and real-world use cases. When it comes to efficiently aggregating string data in PostgreSQL, STRING_AGG() emerges as an invaluable ally in your database toolkit, streamlining your data manipulation processes and enhancing your data management capabilities.