Qualsiasi tipo di PostgreSQL può essere un array. Dichiara la colonna come text[] o int[], scrivi i valori come '{a,b}' oppure ARRAY['a', 'b'], e leggi gli elementi con tags[1] (gli indici partono da 1). Per verificare se un array contiene un valore, usa 'value' = ANY(tags) oppure tags @> ARRAY['value'].
I post 1 e 2 hanno il tag sql. tags[1] è il primo elemento e cardinality conta gli elementi. Le due forme letterali salvano lo stesso valore; ARRAY[...] è più comodo quando gli elementi vengono da espressioni o contengono virgole.
Verificare se un array contiene un valore
Quattro operatori coprono l'appartenenza:
| Test | Significato | Vero per '{sql,json}' |
|---|---|---|
'sql' = ANY(tags) | un valore è nell'array | sì |
tags @> '{sql,json}' | l'array li contiene tutti | sì |
tags <@ '{sql,json,go}' | ogni elemento è in questa lista | sì |
tags && '{go,json}' | gli array hanno almeno un elemento in comune | sì |
Solo il post 1 ha entrambi i tag. I post 2 e 3 hanno un elemento in comune con {go,json}. Il post 3 è l'unico in cui no_go è falso. ALL è il corrispettivo di ANY: il confronto deve valere per ogni elemento.
= ANY segue le regole di SQL sul NULL. Se il valore non viene trovato e l'array contiene un NULL, il risultato è NULL, non falso. Quindi WHERE NOT ('x' = ANY(tags)) può scartare righe che ti aspettavi. @> e && restituiscono vero o falso.
Un indice GIN rende veloci @>, <@, && e = sulle tabelle grandi. Non aiuta = ANY(column), quindi per le ricerche su indice scrivi tags @> ARRAY['sql']:
CREATE INDEX posts_tags_idx ON posts USING gin (tags);
Trasformare un array in righe con unnest
unnest restituisce una riga per elemento, così puoi contare, fare join o filtrare sui singoli elementi:
sql compare in due post e gli altri tag in uno ciascuno. Aggiungi WITH ORDINALITY (unnest(tags) WITH ORDINALITY AS t(tag, pos)) per avere anche la posizione di ogni elemento. Per raccogliere di nuovo le righe in un array, usa array_agg (vedi string_agg e array_agg).
Aggiungere, rimuovere e modificare elementi
Il risultato è {1,2,3}, {1,2,3,4}, {2,3}, {1,9,3}, 2, {20,30} e a, b, c. In un UPDATE le stesse funzioni modificano un array salvato: SET tags = array_append(tags, 'new') oppure SET tags = array_remove(tags, 'old'). array_remove elimina ogni copia del valore. string_to_array('a,b,c', ',') divide un testo in un array.
Ottenere la lunghezza di un array
cardinality(tags) restituisce il numero di elementi, e 0 per un array vuoto. array_length(tags, 1) restituisce la lunghezza della dimensione 1, e NULL per un array vuoto. Quindi WHERE array_length(tags, 1) = 0 non trova mai niente. Per riconoscere un array vuoto usa cardinality(tags) = 0 oppure tags = '{}'.
Gli array di PostgreSQL possono avere più di una dimensione (int[][]), ma ogni sotto array deve avere la stessa lunghezza, e la dimensione dichiarata (int[3]) non viene fatta rispettare. Per dati annidati o irregolari, di solito JSONB è una scelta migliore.
Domande frequenti
Gli array di PostgreSQL partono da 0 o da 1?
Da 1, per impostazione predefinita: tags[1] è il primo elemento. Un indice oltre la fine restituisce NULL invece di generare un errore.
Che differenza c'è tra IN e = ANY in PostgreSQL?
x IN (1, 2, 3) prende una lista scritta nella query; x = ANY(array) prende un singolo valore array. ANY è comodo nel codice dell'applicazione, perché puoi passare un solo parametro array (WHERE id = ANY($1)) invece di costruire una lista di segnaposto.
Meglio una colonna array o una tabella separata?
Un array va bene per una lista corta che leggi e scrivi insieme alla sua riga, come i tag. Quando gli elementi hanno bisogno di colonne proprie, di chiavi esterne, o vengono aggiornati uno per uno, una tabella separata con una riga per elemento è più facile da interrogare e da vincolare.
Come verifico se un array è vuoto in PostgreSQL?
Usa cardinality(arr) = 0 oppure arr = '{}'. array_length(arr, 1) = 0 non è mai vero, perché array_length restituisce NULL per un array vuoto.