How to remove duplicates, which are generated with array_agg postgres function

Question

Does anyone an idea how to rewrite following SQL query to generate results, that would contains only one occurrence of name? (results grouped by user).

The query

SELECT array_to_string(array_agg(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

Executable query is avaiable on sqlfiddle.com. Click on Run SQL button and you will result, which contains Frantisek Smith twice

In postgre 9.0+ there is `string_agg(text,text)` function. It may be easier to write `string_agg(CONCAT(...),', ')` — Ihor Romanchenko, Oct 14 '14 at 15:30
@a_horse_with_no_name distinct works fine, but scrambles the order. Any way to preserve it? — Rodrigo, Mar 03 '16 at 13:57
@a_horse_with_no_name I tried string_agg(distinct nome order by l.id,', ') and string_agg(distinct nome order by nome,', '), but both give "function string_agg(character varying) does not exist". I'll ask a question with the full sql to show you. — Rodrigo, Mar 03 '16 at 14:00
@a_horse_with_no_name Now it gives "in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list" — Rodrigo, Mar 03 '16 at 14:10
@a_horse_with_no_name please take a look: http://stackoverflow.com/q/35774675/1086511 — Rodrigo, Mar 03 '16 at 14:18

score 167 · Accepted Answer · answered Oct 14 '14 at 14:52

167

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

answered Oct 14 '14 at 14:52

Mureinik

297,002
52
306
350

6

Any way to preserve the order using distinct? – Rodrigo Mar 03 '16 at 13:57
4

ERROR: DISTINCT is not implemented for window functions SQL State :0A000 (PostgreSQL 9.5.5) – P01550n Dec 05 '16 at 13:57
2

Saved me from having to write my own aggregate function! – Thalis K. Oct 14 '17 at 11:58
Wouldn't `STRING_AGG` be better? See my answer. – Vérace Nov 01 '21 at 09:27

score 5 · Answer 2 · answered Nov 01 '21 at 09:26

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

How to remove duplicates, which are generated with array_agg postgres function

2 Answers2

Linked