Błąd Postgres [kolumna musi pojawić się w klauzuli GROUP BY lub zostać użyta w funkcji agregującej], gdy używane jest zapytanie podrzędne

Mam dwa stoliki employeei phones. Pracownik może mieć od 0 do n numerów telefonów. Chcę wymienić nazwiska pracowników wraz z ich numerami telefonów. Korzystam z poniższego zapytania, które działa poprawnie.

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

Tabela pracowników może zawierać dużą liczbę wierszy. Chcę pobierać tylko niektórych pracowników jednocześnie. Na przykład chcę pobrać 3 pracowników z ich numerami telefonów. Próbuję uruchomić to zapytanie.

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

Ale dostaję ten błąd. ERROR: column "employee.empname" must appear in the GROUP BY clause or be used in an aggregate function Jedyną różnicą między dwoma zapytaniami jest to, że używam zapytania podrzędnego w tym drugim, aby ograniczyć wiersze przed dołączeniem. Jak rozwiązać ten błąd?

Funkcja Postgres, aby móc używać klucza podstawowego tabeli GROUP BYi nie trzeba dodawać innych kolumn tej tabeli w GROUP BYklauzuli, jest stosunkowo nowa i działa tylko w przypadku tabel podstawowych. Optymalizator nie jest (jeszcze?) Wystarczająco sprytny, aby zidentyfikować klucze podstawowe widoków, ctes lub tabel pochodnych (jak w twoim przypadku).

Możesz dodać kolumny, które chcesz w SELECTdo GROUP BYklauzuli:

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 ;

lub użyj podzapytania (i przenieś GROUP BYtam):

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 ;

który można również zapisać jako:

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 ;

Ponieważ jesteś w wersji 9.3+. możesz również użyć LATERALsprzężenia:

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 ;