How to use string_agg function in PostgreSQL with examples

In this blog post, I will show the usage of string_agg function in PostgreSQL with examples.

As name suggests, string_agg is a string aggregate function which is used to concatenate values from multiple rows into one.

Syntax

SELECT string_agg(<column-name>, 'delimiter') FROM table;
Code language: Java (java)

Usage

Lets first create a user table to show the usage.

CREATE TABLE IF NOT EXISTS test."user" ( id numeric NOT NULL, name character varying(255) NOT NULL, email character varying(255) , CONSTRAINT user_pkey PRIMARY KEY (id) );
Code language: Java (java)

put some data into the table

INSERT INTO test."user"(id, name, email) VALUES (1, 'A', '[email protected]'), (2, 'B', '[email protected]'), (3, 'C', '[email protected]'), ... (16, 'Y', '[email protected]'), (17, 'X', '[email protected]'), (18, 'S', '[email protected]'), (19, 'R', null), (20, 'V', null) ;
Code language: Java (java)

Below query gives all the names in table

select name from "test".user;
Code language: Java (java)

If you want to get the comma separated name list, you can use the string_agg function

select string_agg(name,',') as names from "test".user;
Code language: Java (java)
string_agg

We can order the contents inside the result using ORDER BY clause

select string_agg(name,',' ORDER BY name) as names from "test".user;
Code language: Java (java)

the function ignores the null values while concatenating strings.

For below query some users email id is null in the table but function ignores those values.

select string_agg(email,',') as emails from "test".user where id in (1,2,3,18,19);
Code language: Java (java)

Now lets see the usage in one-to-many relationship

Let’s create user_role table which holds multiple roles for each user.

CREATE TABLE IF NOT EXISTS test.user_role ( id numeric NOT NULL, user_id numeric, role character varying(255) NOT NULL, CONSTRAINT user_role_pkey PRIMARY KEY (id), CONSTRAINT userid_fk FOREIGN KEY (user_id) REFERENCES test."user" (id) );
Code language: Java (java)

Let’s insert some data

INSERT INTO test.user_role(id, user_id, role) VALUES (1, 1, 'SUPER_ADMIN'), (2, 1, 'EDITOR'), (3, 1, 'AUTHOR'), ..... (15, 8, 'CONTRIBUTOR') ;
Code language: Java (java)

Now lets list all the roles each user has

select u.name , u.role from "test".user u, "test".user_role ur where u.id = ur.user_id;
Code language: Java (java)

The above query list each role in different row. If you want to display roles belonging to each role on a single row, you can use below query

select u.name, string_agg(ur.role,',') from "test".user u , "test".user_role ur where u.id = ur.user_id GROUP BY name;
Code language: Java (java)

You can also order the roles using ORDER BY clause

select u.name, string_agg(ur.role,' , ' ORDER BY role) from "test".user u , "test".user_role ur where u.id = ur.user_id GROUP BY name;
Code language: Java (java)

You can also use DISTINCT function along with string_agg function to display unique comma separated roles

select string_agg(distinct(role),' , ' ORDER BY role) as role from "test".user_role;
Code language: Java (java)

You can download the SQL script and queries for this blog post from the GitHub

Similar Posts