Utilizzo degli array in PostgreSQL: una guida

Quando pensi alle immagini SQL di tabelle, probabilmente ti vengono in mente le query SELECT e il concetto di una riga per punto dati senza struttura nidificata.

Ma cosa succederebbe se volessi memorizzare un elenco di numeri, prodotti e altro in una riga?

È possibile espandere l'elenco in più righe, ma ciò potrebbe portare a una giungla di righe di cui potrebbe essere difficile tenere traccia. Fortunatamente, PostgreSQL offre il tipo di dati array che può essere utilizzato per archiviare e manipolare elenchi.

Con ARRAY, PostgreSQL consente di definire le colonne di una tabella come array multidimensionali di lunghezza variabile. È possibile creare array di qualsiasi tipo base integrato o definito dall'utente, tipo enum, tipo composito, tipo intervallo o dominio.

Gli array possono assumere molte forme ed esistono diversi modi per dichiararli.

È possibile utilizzare la parola chiave ARRAY dopo la dichiarazione del tipo di dati per una colonna per indicare che si desidera creare un array del tipo di dati precedentemente dichiarato, come mostrato di seguito.

Potresti anche seguire la dichiarazione del tipo di dati con parentesi quadre, come text[], ma trovo che sia meno esplicito. E non è conforme allo standard SQL, a differenza della versione della parola chiave.

Nell'esempio seguente, creo una piccola tabella per simulare una carta della spesa e popolarla con alcune permutazioni di più prodotti. Se esegui la query completa, puoi anche vedere come gli array vengono rappresentati come output.

Un altro modo per aggiungere un array a una tabella è utilizzare la sintassi {} invece della parola chiave ARRAY tra parentesi. Entrambi i modi funzionano. Devi solo prestare molta attenzione alle virgolette tra parentesi graffe. Ancora una volta, trovo la sintassi delle parole chiave più esplicita e le parentesi più vicine alle rappresentazioni degli array in altri linguaggi di programmazione, ma entrambi funzionano bene.

Altro su Data Science10 concetti SQL avanzati che dovresti conoscere per le interviste di Data Science

Cosa succederebbe se avessi effettivamente bisogno di esplodere o disnidificare l'array per righe specifiche per unirti ad alcune tabelle di informazioni sul prodotto o eseguire altre operazioni in base alle voci dell'elenco?

Nessun problema. Gli array in PostgreSQL possono essere facilmente non nidificati con la parola chiave UNNEST:

Come previsto, UNNEST crea una riga per elemento nel rispettivo array nella riga originale:

Potresti chiederti se è necessario annullare l'annidamento di un array ogni volta che desideri accedere a un singolo elemento per ulteriori operazioni. Se è così, dopo tutto una struttura ad array potrebbe non essere una buona idea. Fortunatamente, gli array in PostgreSQL consentono l'accesso tramite l'indicizzazione con parentesi e sezioni, come mostrato di seguito. Ciò consente una facile selezione, ad esempio, del primo elemento di ogni elenco o di un intervallo specifico di elementi in un elenco.

L'indicizzazione negli array PostgreSQL inizia da uno, non da zero, il che potrebbe differire da quello a cui sei abituato in altri linguaggi di programmazione.

Le porzioni funzionano con la sintassi [start:end]. Accesso agli elementi dell'array tramite suddivisione per tutte le righe con più di due elementi dell'array.

Se non sei sicuro di quanti elementi ha il tuo array o se vuoi filtrare in base alla dimensione di un array, come ho fatto sopra, puoi utilizzare la parola chiave CARDINALITY. Ciò restituirà il numero di elementi nell'array come numero intero.

Cosa succederebbe se volessi selezionare tutti i carrelli degli acquisti che hanno un prodotto specifico nell'array? O qualsiasi altra operazione di filtro basata su un elemento dell'array? Nessun problema, PostgreSQL ti consente di utilizzare il valore dell'elemento nella clausola WHERE insieme alla parola chiave ANY.

Di seguito sto filtrando le righe che contengono "prodotto_c" nella colonna dell'array prodotti. Questo modo di filtrare è particolarmente utile per i valori di filtro singolo.

Potresti anche estendere la logica del filtro dall'alto e chiedere qualsiasi carta acquisti che contenga un sottoarray specifico. Qui sto utilizzando l'operatore '@>', che sta per 'contiene'. Può essere letto come segue:

"L'array del prodotto contiene l'array ['prodotto_a', 'prodotto_b']."

Gli array e i valori degli array possono essere aggiornati in modo simile ad altri tipi di dati, con una clausola UPDATE...SET..., come mostrato di seguito. È possibile aggiornare un singolo elemento dell'array tramite l'indicizzazione o l'intero array.