What is the Postgresql opposite of the unnest function to create an array from a resultset

user_1000753 0
Viewed 2731

I have a dept --< emp schema. I want create a resultset which joins dept and emp with a row for each dept and a column which is an array of emps in each dept.

So something like select dept_name, xxxxxxx from dept,emp where emp_dept_id = dept_id

returning

department1 | fred,bill,joe
department2 | faith, hope, charity
postgresql sql
1 Answers

I would recommend arrays:

select d.dept_name, array_agg(e.name)
from dept d join 
     emp e
     on e.emp_dept_id = d.dept_id
group by d.dept_name;

You can also use string_agg(e.name, ',') if you really prefer a string.

Notes:

  • Never use commas in the FROM clause.
  • Always use proper, explicit, standard, readable JOIN syntax.
  • Qualify all column names in a query that references multiple tables.
  • Use table aliases so the query is easier to write and read.
user_11440350
Related