EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL STRING_AGG()
 

PostgreSQL STRING_AGG()

Updated May 24, 2023

PostgreSQL STRING_AGG()

 

 

Introduction to PostgreSQL STRING_AGG()

PostgreSQL supports various kinds of aggregate functions, The STRING_AGG() function is one of the aggregate functions 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 by PostgreSQL 9.0 version, which performs the aggregate option related to the string. We can use various separators or delimiter symbols to concatenate the strings.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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 that is any valid expression.
  2. separator/delimiter: This defines the separator/delimiter used for string concatenation.

The ORDER BY clause is optional and 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 also use other data types but only need to ensure 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(), 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 above INSERT statement’s result using the following SQL statement and snapshot.

select * from course;

PostgreSQL STRING_AGG() output 1

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 above INSERT statement’s result using the following SQL statement and snapshot.

select * from student;

PostgreSQL STRING_AGG() output 2

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.

PostgreSQL STRING_AGG() output 3

In the above example, the resulting snapshot has 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.

output 4

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.

output 5

In the above example, the resulting snapshot shows us the students concatenated by a comma separator with 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.

output 6

In the above example, we observe that the code groups and concatenates all students from the same country, utilizing a comma separator.

Advantages

  1. We can control the order of the result by using the 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 between them.
  4. The PostgreSQL STRING_AGG() supports various types of delimiter symbols or separators and does not include delimiter symbols or separators at the end of the string.

Conclusion

From the above article, we hope you understand 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

We hope that this EDUCBA information on “PostgreSQL STRING_AGG()” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

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

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW