String_agg and String_to_array functions in PostgreSQL

String_agg()

1) Concatenates a list of strings and places a separator between them.

2) The function does not add the separator at the end of the string.

3) The function accepts two arguments expression and separator and an optional ORDER BY clause.

expression is any valid expression that can resolve to a character string. If you use other types than character string type, you need to explicitly castthese values of that type to the character string type.

separator is the separator for concatenated strings.

order_by_clause is an optional clause that specifies the order of concatenated results.

String_to_array()

This function is used to split a string into array elements using supplied delimiter and optional null string.

Unnest()

This function is used to expand an array to a set of rows.

Example 1:

Let’s consider an example to understand these functions

Suppose we have a table called names_sort1 with columns id and names.

create table names_sort1
(id int primary key,
names varchar(200)
);

Add some data to our table names_sort1

insert into names_sort1 values
(1,‘Kery,Walter,Henry,Thalia,Dale,Cheryl,Silas,Pearl,Henry,Mark,Sawyer,Bev,Nathan,Marvan,Adlin,Graham,Pearl’),(2, ‘Kery,Walter,Henry,Thalia,Dale’);

Check data in our table

select id , names from names_sort1;

Convert string to the array by using the string_to_array() function

select id , string_to_array(names, ‘,’) as order
from names_sort1
order by string_to_array(names, ‘,’) asc, id;

select id , unnest(string_to_array(names, ‘,’)) as order
from names_sort1
order by unnest(string_to_array(names, ‘,’)) asc, id;

Now group names by id and distinct names in ascending

select x.id ,string_agg(distinct x.order,’,’) as names from
(select id , unnest(string_to_array(names, ‘,’)) as order
from names_sort1
order by unnest(string_to_array(names, ‘,’)) asc, id
)x
group by x.id
order by x.id asc;

This query makes use of string_to_array(), string_agg() and unnest() functions.

Example 2:

create table entries (
name varchar(20),
address varchar(20),
email varchar(20),
floor int,
resources varchar(10));

insert into entries
values (‘A’,’Bangalore’,’A@gmail.com’,1,’CPU’),(‘A’,’Bangalore’,’A1@gmail.com’,1,’CPU’),(‘A’,’Bangalore’,’A2@gmail.com’,2,’DESKTOP’)
,(‘B’,’Bangalore’,’B@gmail.com’,2,’DESKTOP’),(‘B’,’Bangalore’,’B1@gmail.com’,2,’DESKTOP’),(‘B’,’Bangalore’,’B2@gmail.com’,1,’MONITOR’)
;
select * from entries;

Method1:

WITH CTE AS
(SELECT NAME,FLOOR AS MOST_VISITED_FLOOR,COUNT(1),ROW_NUMBER()OVER(PARTITION BY NAME ORDER BY COUNT(1) DESC) AS R
FROM ENTRIES GROUP BY NAME, FLOOR
)
SELECT C.NAME, C.MOST_VISITED_FLOOR,COUNT(*), STRING_AGG(DISTINCT RESOURCES,’,’) FROM CTE C,
ENTRIES E WHERE C.NAME = E.NAME AND C.R = 1 GROUP BY C.NAME, C.MOST_VISITED_FLOOR;

Method2:

select name, count(name) as tota_visit,
sum(case when floor = (select max(floor) from entries) then 1 else 0 end) as most_visited,
STRING_AGG(DISTINCT RESOURCES,’,’) “resources_used”
from entries
group by name;

Thanks for the read.Do clap if find it useful.🙂

“Keep learning and keep sharing knowledge”