How to remove duplicates, which are generated with array_agg postgres function
You can use the distinct
keyword inside array_agg
:
SELECT ARRAY_TO_STRING(ARRAY_AGG(DISTINCT CONCAT(u.firstname, ' ', u.lastname)), ', ')
FROM log_has_item logitem
INNER JOIN log log ON log.id = logitem.log_id
INNER JOIN worker u ON log.worker_id = u.id
WHERE logitem.company_id = 1
SQLFiddle with this example
No need to go all round the houses with ARRAY_TO_STRING(ARRAY_AGG(
when a simple STRING_AGG
will do as follows (code available on the fiddle here):
--
-- Simplified
--
SELECT
DISTINCT
STRING_AGG
(
DISTINCT CONCAT(w.firstname, ' ', w.lastname), ', '
) AS "The workers"
FROM log_item li
INNER JOIN log l ON li.log_id = l.id
INNER JOIN worker w ON l.worker_id = w.id
WHERE li.company_id = 1;
Result:
The workers
Frantisek Smith, Peter Duff