14

I have two tables employee and phones. An employee can have 0 to n phone numbers. I want to list the employee names with their phone numbers. I am using the below query which runs fine.

SELECT empname,array_agg(phonenumber) AS phonenumbers 
FROM employee LEFT OUTER JOIN phones ON employee.empid = phones.empid
GROUP BY employee.empid

enter image description here

The employee table might contain large number of rows. I want to fetch only some employees at a time. For example I want to fetch 3 employees with their phone numbers. I am trying to run this query.

SELECT empname,array_agg(phonenumber) AS phonenumbers 
FROM 
(SELECT * FROM employee ORDER BY empname LIMIT 3 OFFSET 0) AS employee 
LEFT OUTER JOIN phones ON employee.empid = phones.empid
GROUP BY employee.empid

But I get this error. ERROR: column "employee.empname" must appear in the GROUP BY clause or be used in an aggregate function The only difference between two queries is that I am using sub query in the latter to limit the rows before joining. How do I solve this error?

20

The feature of Postgres to be able to use the primary key of a table with GROUP BY and not need to add the other columns of that table in the GROUP BY clause is relatively new and works only for base tables. The optimizer is not (yet?) clever enough to identify primary keys for views, ctes or derived tables (as in your case).

You can add the columns you want in the SELECT into the GROUP BY clause:

SELECT e.empname, array_agg(p.phonenumber) AS phonenumbers 
FROM 
(SELECT * FROM employee ORDER BY empname LIMIT 3 OFFSET 0) AS e 
LEFT OUTER JOIN phones AS p ON e.empid = p.empid
GROUP BY e.empid, e.empname 
ORDER BY e.empname ;

or use a subquery (and transfer the GROUP BY there):

SELECT e.empname,
       (SELECT array_agg(p.phonenumber) 
        FROM phones AS p
        WHERE e.empid = p.empid
       ) AS phonenumbers 
FROM 
(SELECT * FROM employee ORDER BY empname LIMIT 3 OFFSET 0) AS e 
ORDER BY e.empname ;

which could also be written as:

SELECT e.empname,
       (SELECT array_agg(p.phonenumber) 
        FROM phones AS p
        WHERE e.empid = p.empid
       ) AS phonenumbers 
FROM employee AS e
ORDER BY e.empname LIMIT 3 OFFSET 0 ;

Since you are in version 9.3+. you can also use a LATERAL join:

SELECT e.empname,
       p.phonenumbers 
FROM 
   (SELECT * FROM employee ORDER BY empname LIMIT 3 OFFSET 0) AS e
LEFT JOIN LATERAL
   (SELECT array_agg(phonenumber) AS phonenumbers
    FROM phones 
    WHERE e.empid = phones.empid
   ) AS p ON TRUE 
ORDER BY e.empname ;
  • @ypercude Thanks. This is a simple and clean way. – Programmer Jan 10 '15 at 9:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.