Postgresql aggregate array

Question

I have a two tables

Student -------- Id  Name 1   John     2   David 3   Will  Grade --------- Student_id  Mark 1           A 2           B 2           B+ 3           C 3           A 

Is it possible to make native Postgresql SELECT to get results like below:

Name    Array of marks ----------------------- 'John',     {'A'} 'David',    {'B','B+'} 'Will',     {'C','A'} 

But not like below

Name    Mark ---------------- 'John',     'A' 'David',    'B' 'David',    'B+' 'Will',     'C' 'Will',     'A' 

Answer 1

Use array_agg: http://www.sqlfiddle.com/#!1/5099e/1

SELECT s.name,  array_agg(g.Mark) as marks         FROM student s LEFT JOIN Grade g ON g.Student_id = s.Id GROUP BY s.Id 

By the way, if you are using Postgres 9.1, you don't need to repeat the columns on SELECT to GROUP BY, e.g. you don't need to repeat the student name on GROUP BY. You can merely GROUP BY on primary key. If you remove the primary key on student, you need to repeat the student name on GROUP BY.

CREATE TABLE grade     (Student_id int, Mark varchar(2));  INSERT INTO grade     (Student_id, Mark) VALUES     (1, 'A'),     (2, 'B'),     (2, 'B+'),     (3, 'C'),     (3, 'A');   CREATE TABLE student     (Id int primary key, Name varchar(5));  INSERT INTO student     (Id, Name) VALUES     (1, 'John'),     (2, 'David'),     (3, 'Will');