Menu

string_agg e array_agg in PostgreSQL (GROUP_CONCAT)

string_agg unisce i valori di un gruppo in una sola stringa e array_agg li raccoglie in un array. Ordinamento, DISTINCT, FILTER, gestione dei NULL e gli errori più comuni.

Questa pagina include editor eseguibili: modifica, esegui e vedi subito l'output.

string_agg(expression, separator) unisce i valori di ogni gruppo in una sola stringa, e array_agg(expression) li raccoglie in un array. Metti ORDER BY dentro la chiamata per controllare l'ordine: string_agg(name, ', ' ORDER BY name).

IT restituisce Bo, Kim, Li e {6100,4800,3900}; Sales restituisce Ana, Raj e {5000,4200}. Entrambe sono normali funzioni di aggregazione come count o sum: lavorano per gruppo con GROUP BY, e sull'intera tabella senza.

Ordinare i valori

Senza ORDER BY dentro l'aggregato, i valori arrivano nell'ordine in cui arrivano le righe, che può cambiare da un'esecuzione all'altra. Un ORDER BY in fondo alla query ordina le righe del risultato, non i valori dentro ogni stringa. L'ORDER BY interno va dopo l'ultimo argomento, senza virgola:

string_agg(name, ', ' ORDER BY hired_on DESC)
array_agg(name ORDER BY score DESC, name)

Togliere i duplicati con DISTINCT

Ada ha comprato una penna due volte, ma DISTINCT la elenca una volta sola: book, pen. Con DISTINCT, l'ORDER BY può usare solo il valore aggregato. Ordinare per un'altra colonna fallisce con "in an aggregate with DISTINCT, ORDER BY expressions must appear in argument list".

Come si comportano NULL e gruppi vuoti

string_agg salta i valori NULL e restituisce Bo, Li. array_agg li tiene: {Bo,Li,NULL}. Per togliere i NULL da array_agg, aggiungi FILTER (WHERE name IS NOT NULL).

Su zero righe entrambe restituiscono NULL, non una stringa vuota o un array vuoto. Avvolgile quando chi le usa si aspetta un valore: COALESCE(string_agg(...), '') oppure COALESCE(array_agg(...), '{}').

Aggregare solo alcune righe con FILTER

FILTER (WHERE ...) limita un aggregato alle righe che corrispondono, mentre il resto della query le vede tutte:

IT ha 3 persone ed elenca Bo, Li; Sales ne ha 2 ed elenca Ana.

Affiancare una lista a ogni riga di un'altra tabella

Per mostrare ogni cliente con la lista dei suoi prodotti, aggrega in una subquery, così la query esterna non ha bisogno di GROUP BY:

SELECT c.id, c.name, c.email,
       (SELECT string_agg(o.product, ', ' ORDER BY o.product)
        FROM orders o
        WHERE o.customer_id = c.id) AS products
FROM customers c;

I clienti senza ordini restano nel risultato, con products a NULL. Una join con GROUP BY li tiene solo se è una LEFT JOIN.

Errori comuni e varianti

  • Numeri: string_agg accetta testo, quindi string_agg(id, ',') fallisce con "function string_agg(integer, unknown) does not exist". Fai un cast: string_agg(id::text, ',').
  • Primi N per gruppo: taglia l'array ordinato, (array_agg(name ORDER BY salary DESC))[1:2].
  • Output JSON: json_agg e jsonb_agg raccolgono le righe in un array JSON, e jsonb_object_agg(key, value) costruisce un oggetto; vedi JSONB.
  • Hai già un array: array_to_string(arr, ', ') unisce un array esistente, e unnest lo trasforma di nuovo in righe (array).
  • Versione finestra: entrambe funzionano anche come funzioni finestra, per esempio string_agg(name, ', ') OVER (PARTITION BY dept) per mostrare l'intera lista su ogni riga.

Domande frequenti

Qual è l'equivalente di GROUP_CONCAT in PostgreSQL?

string_agg(column, ','). A differenza di GROUP_CONCAT di MySQL, il separatore è obbligatorio e l'ordinamento va dentro la chiamata: string_agg(name, ', ' ORDER BY name).

Come uso string_agg con i numeri?

Converti il valore in testo: string_agg(id::text, ','). string_agg accetta solo testo (o bytea), quindi con un argomento intero fallisce con "function string_agg(integer, unknown) does not exist".

string_agg ha un limite di lunghezza?

Nessun limite a parte; il risultato è un normale valore text, che può arrivare a 1 GB. GROUP_CONCAT di MySQL tronca a group_concat_max_len, mentre PostgreSQL non tronca.

Come ottengo i primi N valori con array_agg?

Ordina dentro l'aggregato e taglia il risultato: (array_agg(name ORDER BY score DESC))[1:3] restituisce i primi tre nomi di ogni gruppo.

Illustrazione dei linguaggi di programmazione di Coddy

Impara a programmare con Coddy

Inizia