EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • 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
  • Login
Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL ARRAY_AGG()

PostgreSQL ARRAY_AGG()

Sohel Sayyad
Article bySohel Sayyad
Priya Pedamkar
Reviewed byPriya Pedamkar

Updated May 29, 2023

PostgreSQL ARRAY_AGG()

Introduction to PostgreSQL ARRAY_AGG()

The PostgreSQL provides various aggregate functions; the PostgreSQL ARRAY_AGG() aggregate function is used to get an array with each value of the input set added to an array element. This aggregate Function accepts a set of values as input, and the Function includes NULL values into the array while concatenating the input values. We use the ORDER BY clause with this aggregate Function in order to sort the result.

ADVERTISEMENT
Popular Course in this category
POSTGRESQL Course Bundle - 5 Courses in 1 | 1 Mock Test

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

Syntax

Consider the following syntax of the PostgreSQL ARRAY_AGG() aggregate Function:

ARRAY_AGG(input_expression [ORDER BY [sort_expression {ASC | DESC}], [...])
  • ARRAY_AGG(): The PostgreSQL ARRAY_AGG() aggregate Function takes the number of values as an input and then returns an array.
  • ORDER BY: This is an optional clause. This clause is used when we want the results sorted, which are processed in the aggregation, which results to sort the elements in the result array.

Examples to Implement PostgreSQL ARRAY_AGG()

We will create two tables of name ‘student’ and ‘department’ by using the CREATE TABLE statement as follows to understand the examples:

create table student
(
stud_id serial PRIMARY KEY,
stud_fname VARCHAR(80) NOT NULL,
stud_lname VARCHAR(80) NOT NULL,
department_id int NOT NULL
);
create table department
(
department_id serial PRIMARY KEY,
department_name VARCHAR(80) NOT NULL
);

We will insert data into the department table using the INSERT INTO statement as follows.

INSERT INTO department(department_name)
VALUES
('Computer'),
('Electrical'),
('IT'),
('Civil'),
('Chemical'),
('Mechanical');

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

Code:

select * from department;

Output:

CREATE TABLE

We will insert some data into the student table using the INSERT INTO statement as follows.

INSERT INTO student(stud_fname, stud_lname, department_id)
VALUES
('Smith','Johnson',1),
('Williams','Jones',1),
('Harper','James',2),
('Jack','Liam',2),
('Harry','Mason',3),
('Jacob','Oscar',3),
('Michael','Charlie',4),
('William','Joe',4),
('Oliver','John',5),
('Jack','Richard',5),
('Harry','Joseph',5),
('George','Thomas',6),
('Brown','Charles',6);

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

Code:

select * from student;

Output:

PostgreSQL ARRAY_AGG() - 2

Example #1

Without ORDER BY clause in PostgreSQL ARRAY_AGG, () aggregate Function Consider the following SQL statement, which will use the Function to return the list of names of the department and the list of names of the students studying in each department:

Code:

SELECT
department_name,
ARRAY_AGG (stud_fname || ' ' || stud_lname) students
FROM
department
INNER JOIN student USING (department_id)
GROUP BY
department_name
ORDER BY
Department_name;

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

PostgreSQL ARRAY_AGG() - 3

From the above example, we can see that each department’s students are randomly ordered; to sort the students by their last name or first name, we have to define the ORDER BY clause in this Function.

Example #2

ORDER BY clause with PostgreSQL ARRAY_AGG() aggregate Function

Consider the following example to get the list of students for each department, which is sorted by the student’s first name as shown in the following SQL statement:

Code:

SELECT
department_name,
ARRAY_AGG (
stud_fname || ' ' || stud_lname
ORDER BY
stud_fname
) students
FROM
department
INNER JOIN student USING (department_id)
GROUP BY
department_name
ORDER BY
department_name;

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

PostgreSQL ARRAY_AGG() - 4

Example #3

ORDER BY clause with PostgreSQL ARRAY_AGG() aggregate Function

Consider the following example to sort the list of students for each department by student’s first and last name, as shown in the following SQL statement:

Code:

SELECT
department_name,
ARRAY_AGG (
stud_fname || ' ' || stud_lname
ORDER BY
stud_fname ASC,
stud_lname DESC
) student
FROM
department
INNER JOIN student USING (department_id)
GROUP BY
department_name
ORDER BY
department_name;

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

ORDER BY

Conclusion

From the above article, we hope you understand how to use the PostgreSQL ARRAY_AGG() aggregate Function and how the aggregate Function works. Also, we have added several examples of aggregate functions to understand them in detail.

Recommended Articles

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

  1. PostgreSQL LAG()
  2. PostgreSQL CONCAT()
  3. Interval PostgreSQL
  4. PostgreSQL Math Functions
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - 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

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

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

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

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW