How to group records in Postgres?

Question

PostgreSQL

There is an example
sqlfiddle.com/#!15/c2f3f/18
How to refine the query so that there are 3 records left, 1 for each record from table1, so that the skills field contains an array of corresponding values ​​from table2, that is:
1 asya [ burn, eat ]
2 petya [code]
3 lena [cook, clean]

Answer 1

use the array_agg function like so:

SELECT table1 .id, table1 .name, array_agg(table2.skills)
FROM table1 
LEFT JOIN table2 ON table2.id = table1.id
GROUP BY table1.id

Answer 2

First, you don't need union, use join . Second, grouping is done with group by . To get an array of grouped values, use the array_agg() aggregate function .

select
  t1.name, array_agg(t2.skills)
from table1 as t1
inner join table2 as t2
  using (id)
group by t1.name;

sql fidle

Answer 3

SELECT id, name, array_agg(skills)
FROM table1 JOIN table2 USING (id)
GROUP BY 1,2