String aggregation with STRING_AGG in PostgreSQL

Introduction

STRING_AGG helps combine many text values from multiple rows into one single text string. It makes it easy to see all related words or names together.

You want to list all the names of students in a class as one sentence.

You need to show all tags related to a blog post in one line.

You want to combine all city names in a country into a single string.

You want to create a comma-separated list of products bought by a customer.

Syntax

PostgreSQL

STRING_AGG(expression, delimiter) [ORDER BY expression]

expression is the column or text you want to join.

delimiter is the text between each value, like a comma or space.

Examples

Combine all student names separated by a comma and space.

PostgreSQL

SELECT STRING_AGG(name, ', ') FROM students;

Combine city names separated by semicolon and space, sorted alphabetically.

PostgreSQL

SELECT STRING_AGG(city, '; ' ORDER BY city) FROM locations;

Combine tags for a blog post with ' | ' as separator.

PostgreSQL

SELECT STRING_AGG(tag, ' | ') FROM blog_tags WHERE post_id = 5;

Sample Program

This creates a table of fruits, adds three fruit names, then combines them into one string sorted alphabetically.

PostgreSQL

CREATE TABLE fruits (id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO fruits (name) VALUES ('Apple'), ('Banana'), ('Cherry');

SELECT STRING_AGG(name, ', ' ORDER BY name) AS fruit_list FROM fruits;

Important Notes

If you do not use ORDER BY inside STRING_AGG, the order of combined strings may be random.

STRING_AGG only works with text data types. Use CAST if needed.

Summary

STRING_AGG joins multiple text values into one string with a chosen separator.

You can sort the values inside STRING_AGG using ORDER BY.

It is useful for showing lists in one row instead of many rows.