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)

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