How to Use string_agg in PostgreSQL: Syntax and Examples

In PostgreSQL, use the string_agg function to combine multiple string values from rows into a single string with a specified separator. The syntax is string_agg(expression, delimiter), where expression is the column or string to combine, and delimiter is the separator between values.

Syntax

The string_agg function combines multiple string values into one string separated by a delimiter.

• expression: The column or string value to aggregate.
• delimiter: The string used to separate the aggregated values.

sql

string_agg(expression, delimiter)

Example

This example shows how to combine the names of employees in each department into a single comma-separated string.

sql

CREATE TABLE employees (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  department TEXT NOT NULL
);

INSERT INTO employees (name, department) VALUES
('Alice', 'Sales'),
('Bob', 'Sales'),
('Charlie', 'HR'),
('Diana', 'HR'),
('Eve', 'IT');

SELECT department, string_agg(name, ', ') AS employee_names
FROM employees
GROUP BY department
ORDER BY department;

Output

 department | employee_names
------------+----------------
 HR         | Charlie, Diana
 IT         | Eve
 Sales      | Alice, Bob

Common Pitfalls

Common mistakes when using string_agg include:

• Not using GROUP BY when aggregating by groups, which causes errors or unexpected results.
• Using NULL values in the expression without handling them, which are ignored by string_agg.
• Forgetting to specify a delimiter, which is required.

sql

/* Wrong: Missing GROUP BY causes error or wrong aggregation */
SELECT department, string_agg(name, ', ')
FROM employees;

/* Right: Use GROUP BY to aggregate per department */
SELECT department, string_agg(name, ', ')
FROM employees
GROUP BY department;

Quick Reference

Summary tips for using string_agg:

• Always provide a delimiter string.
• Use GROUP BY to aggregate by groups.
• Null values in the expression are skipped.
• Works with any string or text expression.

Key Takeaways

Use string_agg(expression, delimiter) to combine multiple strings into one with a separator.

Always use GROUP BY when aggregating by groups to get correct results.

Null values in the aggregated column are ignored by string_agg.

The delimiter is required and defines how values are separated in the result.

string_agg works with any text or string expression in PostgreSQL.