PostgreSQL STRING_AGG()

Introduction to PostgreSQL STRING_AGG()

PostgreSQL supports various kind of aggregate function, The STRING_AGG() function is one of the aggregate function which is used to concatenate the list of strings and it will add a place to a delimiter symbol or a separator between all of the strings.  The separator or a delimiter symbol will not be included at the end of the output string. The PostgreSQL STRING_AGG() function is supported from PostgreSQL 9.0 version which is used to perform the aggregate option related to the string. We can use various types of separators or delimiter symbols for concatenating the strings.

Syntax

Consider the following syntax:

STRING_AGG ( expression, separator|delimiter [order_by] )

Explanation:

The STRING_AGG() function takes input ORDER BY clause is an optional and other two arguments as follows:

1. expression: This is a character string which is any valid expression.
2. separator/delimiter: This defines the separator/delimiter which will be used for string concatenation.

The ORDER BY clause is an optional clause, which defines the order of concatenated string results.

The ORDER BY has the syntax as follows:

ORDER BY expression1 {ASC | DESC}, [...]

How does PostgreSQL STRING_AGG() function works?

1. The input expression needed should be a character string data type. We can use other data types as well but only we need to make sure that we have explicitly cast other data types to the character string data type.
2. The PostgreSQL STRING_AGG() returns us the result in string type.
3. The STRING_AGG() is generally used with the GROUP BY clause like we use other PostgreSQL aggregate functions such as MIN(),  MAX(), AVG(), SUM() and  COUNT() etc.

Examples to Implement PostgreSQL STRING_AGG() function

We will create a table named ‘student’ and ‘course’ by using the CREATE TABLE statement as follows:

STUDENT TABLE:

create table student
(
stud_id serial PRIMARY KEY,
stud_name VARCHAR(80) NOT NULL,
stud_grade CHAR(1) NOT NULL,
stud_country VARCHAR(80) NOT NULL,
course_id int NOT NULL
);

COURSE TABLE:

create table course
(
course_id serial PRIMARY KEY,
course_name VARCHAR(80) NOT NULL
);

Now, we will insert some data into the ‘course’ table by using the INSERT INTO statement as follows:

INSERT INTO course(course_name)
VALUES
('Computer'),
('Mechanical'),
('Civil'),
('Electrical');

Illustrate the result of the above INSERT statement by using the following SQL statement and snapshot.

select * from course;

Now, we will insert some data into the ‘student’ table by using the INSERT INTO statement as follows:

INSERT INTO student(stud_name,stud_grade,stud_country,course_id)
VALUES
('Smith','A','USA',1),
('Johnson','B','USA',2),
('Williams','C','USA',3),
('Jones','C','Canada',1),
('Brown','B','Canada',2),
('Davis','A','Canada',3),
('Aarnav','A','India',1),
('Aarush','B','India',2),
('Aayush','C','India',3),
('Abdul','C','UAE',1),
('Ahmed','A','UAE',3),
('Ying', 'A','China',1),
('Yue','B','China',2),
('Feng', 'C','China',3),
('Mian','C','South Korea',1),
('Fei','B','South Korea',2),
('Hong','A','South Korea',3);

Illustrate the result of the above INSERT statement by using the following SQL statement and snapshot.

select * from student;

SELECT c.course_name AS "course name", s.stud_name AS "student name"
FROM course c RIGHT JOIN student s ON c.course_id = s.course_id
ORDER BY 1;

Illustrate the result of the above statement by using the following snapshot.

In the above example,  the resulting snapshot is having each row as a separate entry for the matched course with the student’s course.

We can concatenate the student names by using the STRING_AGG() function by modifying the above SQL statement as follows:

SELECT
crs.course_name AS "course name",
string_agg(stud.stud_name, ', ') AS "student list"
FROM course crs
JOIN student stud ON crs.course_id = stud.course_id
GROUP BY 1
ORDER BY 1;

Illustrate the result of the above statement by using the following snapshot.

SELECT  stud_grade, STRING_AGG(stud_name,', ') AS StudentsPerGrade
FROM student
GROUP BY stud_grade
ORDER BY 1 ;

Illustrate the result of the above statement by using the following snapshot.

In the above example, the resulting snapshot shows us the list of the students concatenated by comma separator having a similar grade obtained.

SELECT STRING_AGG(stud_name, ', ') AS "student_names", stud_country
FROM student
GROUP BY stud_country;

Illustrate the result of the above statement by using the following snapshot.

In the above example, we can see the all of the students belonging to the same country are grouped together and concatenate by a comma separator

Advantages

1. We can control the order of the result by using ORDER BY clause.
2. The PostgreSQL STRING_AGG() function returns the result in string format.
3. We can use the STRING_AGG() function to concatenate all strings and add a delimiter symbol or separator in between them.
4. The PostgreSQL STRING_AGG() supports various types of delimiter symbol or separator and does not include delimiter symbol or separator at the end of the string.

Conclusion

We hope from the above article you have understood how to use the PostgreSQL STRING_AGG() function and how the PostgreSQL STRING_AGG() function works. Also, we have added several examples of the PostgreSQL STRING_AGG() function to understand it in detail.

Recommended Articles

This is a guide to PostgreSQL STRING_AGG(). Here we discuss the Examples to Implement PostgreSQL STRING_AGG() function along with the advantages. You may also have a look at the following articles to learn more –

1. PostgreSQL UNIQUE Constraint
2. PostgreSQL Administration
3. PostgreSQL LAG()
4. SQL RANK()