Updated May 29, 2023
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.
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:
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:
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.
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.
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.
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.
