How to Use string_agg() in PostgreSQL

The `string_agg()` function in PostgreSQL is a powerful tool for concatenating values from multiple rows into a single string. It is often used when you need to combine values from different rows of a table into a single string result.

Let's dive into how to use this function with some real-world examples.

Syntax

string_agg(expression, delimiter)

The `string_agg()` function takes two arguments:

• expression: The column or expression whose values you want to aggregate into a string.
• delimiter: The separator string that will be placed between the aggregated values.

Example 1: Basic Usage

Let's say we have a table called employees with the following data:

+----+------------+
| id | name       |
+----+------------+
|  1 | John       |
|  2 | Jane       |
|  3 | Bob        |
+----+------------+

We can use string_agg() to concatenate all the employee names into a single string:

SELECT string_agg(name, ', ') FROM employees;

The result will be:

John, Jane, Bob

Example 2: Using string_agg() with GROUP BY

In some cases, you might want to group the results by another column. For example, if we had a table of orders and wanted to list all the items for each order, we could use the following query:

SELECT order_id, string_agg(item_name, ', ') 
FROM orders
GROUP BY order_id;

This would give us a concatenated list of items for each order.

Advanced Usage: Ordering the Aggregated Values

You can also specify the order in which the values are concatenated. This can be done by adding an ORDER BY clause within the string_agg() function.

SELECT string_agg(name, ', ' ORDER BY name ASC) FROM employees;

The result will be:

Bob, Jane, John

Conclusion

The string_agg() function is an excellent tool for aggregating and concatenating values in PostgreSQL. Whether you're working with simple data or complex groupings, this function can help you consolidate your results into a single string, making your queries more effective.