Lavorare con gli array in PostgreSQL

Lavorare con gli array in PostgreSQL

Ora che conosciamo le basi, andiamo più a fondo nella creazione pratica degli array nelle query SQL. Qui inizia il bello!

Uso del costruttore ARRAY[] in SELECT

Il costruttore ARRAY[] è super comodo nelle query SELECT quando vuoi creare esplicitamente un array. È come dire a PostgreSQL: “Ehi, ecco un array!”

-- Creazione di un array di numeri
SELECT ARRAY[1, 2, 3, 4, 5] AS numeri;

-- Creazione di un array di stringhe
SELECT ARRAY['Lunedì', 'Martedì', 'Mercoledì'] AS giorni_settimana;

Vantaggi di ARRAY[] rispetto alla sintassi {}

1. Cast esplicito dei tipi:

-- Con ARRAY[] puoi specificare esplicitamente il tipo
SELECT ARRAY['2023-01-01'::DATE, '2023-12-31'::DATE] AS date_importanti;

-- Con {} devi stare più attento
SELECT '{"2023-01-01", "2023-12-31"}'::DATE[] AS date_importanti;

2. Migliore leggibilità nelle query complesse:

SELECT
product_name,
ARRAY[category, subcategory, brand] AS gerarchia_prodotto
FROM products;

Esempio: creazione di un array di numeri

Partiamo dal classico. Supponiamo che tu debba creare un array di numeri:

SELECT ARRAY[1, 2, 3, 4, 5] AS mio_array;

Il risultato sarà:

Nota: PostgreSQL restituisce l’array nel formato {} — è solo il suo modo di mostrare che si tratta di un array. Lo stile è particolare, ma ci si abitua in fretta.

Esempio: creazione di un array di stringhe

Se ti servono delle stringhe invece dei numeri, basta aggiungere le virgolette:

SELECT ARRAY['mela', 'banana', 'arancia'] AS frutti;

Risultato:

Tra l’altro, PostgreSQL adora semplificarti la vita. Anche se usi il cirillico o qualsiasi altro alfabeto, gli array funzionano comunque alla grande.

Esempio: array con dati di altri tipi (tipo date)

E se volessimo mettere un array di date? Facile facile:

SELECT ARRAY['2023-01-01'::DATE, '2023-12-31'::DATE] AS date_importanti;

Risultato:

Nota il ::DATE. Abbiamo detto esplicitamente a PostgreSQL che si tratta di tipo DATE. Senza questo, prenderebbe le stringhe così come sono, e non sarebbe proprio corretto per le date.

Aggregare dati in array con array_agg()

Ora passiamo a una parte più tosta e interessante. E se abbiamo già una tabella con dei dati e vogliamo raggrupparli in un array? Qui entra in gioco la funzione array_agg().

Una delle cose più potenti — trasformare tante righe in array usando array_agg().

Uso base:

-- Creiamo una tabella di test
CREATE TEMP TABLE studenti (
group_id INTEGER,
student_name TEXT
);

INSERT INTO studenti VALUES
(1, 'Anna'), (1, 'Otto'), (1, 'Maria'),
(2, 'Alekse'), (2, 'Kira'),
(3, 'Elena');

-- Raggruppiamo gli studenti per gruppo
SELECT
group_id,
array_agg(student_name) AS studenti
FROM studenti
GROUP BY group_id
ORDER BY group_id;

Ordinare gli elementi nell’array:

SELECT
group_id,
array_agg(student_name ORDER BY student_name) AS studenti_ordinati
FROM studenti
GROUP BY group_id;

Filtrare durante l’aggregazione:

SELECT
group_id,
array_agg(student_name) FILTER (WHERE student_name LIKE 'A%') AS studenti_a
FROM studenti
GROUP BY group_id;

Esempi pratici di utilizzo

Gli array sono utili in un sacco di situazioni quotidiane: dal salvare tag e permessi di accesso al raccogliere le azioni di un utente durante la giornata. Qui sotto trovi degli esempi per capire meglio come e dove usare gli array in PostgreSQL.

Esempio 1: Sistema di tag per un blog

CREATE TABLE blog_posts (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    content TEXT,
    tags TEXT[]
);

-- Insert con sintassi diverse
INSERT INTO blog_posts (title, content, tags) VALUES
    ('Impariamo PostgreSQL', 'Contenuto dell\'articolo...',
     ARRAY['PostgreSQL', 'SQL', 'Database']),
    ('Sviluppo web nel 2024', 'Contenuto dell\'articolo...',
     '{"JavaScript", "React", "Node.js"}'),
    ('Machine learning', 'Contenuto dell\'articolo...',
     ARRAY['ML', 'Python', 'Data Science']);

-- Cercare articoli per tag
SELECT title FROM blog_posts
WHERE 'PostgreSQL' = ANY(tags);

Esempio 2: Sistema di permessi utenti

CREATE TABLE utenti (
id SERIAL PRIMARY KEY,
username TEXT NOT NULL,
permissions TEXT[]
);

INSERT INTO utenti (username, permissions) VALUES
('admin', ARRAY['read', 'write', 'delete', 'manage_users']),
('editor', ARRAY['read', 'write']),
('viewer', ARRAY['read']);

-- Aggregare tutti i permessi unici nel sistema
SELECT array_agg(DISTINCT permesso) AS tutti_permessi
FROM utenti, unnest(permissions) AS permesso;

Esempio 3: Cronologia delle azioni utente

CREATE TABLE azioni_utente (
user_id INTEGER,
azione TEXT,
data_azione DATE
);

INSERT INTO azioni_utente VALUES
(1, 'login', '2024-01-01'),
(1, 'view_profile', '2024-01-01'),
(1, 'edit_settings', '2024-01-01'),
(2, 'login', '2024-01-01'),
(2, 'logout', '2024-01-01');

-- Raggruppare le azioni dell’utente per giorno
SELECT
user_id,
data_azione,
array_agg(azione ORDER BY azione) AS azioni_giornaliere
FROM azioni_utente
GROUP BY user_id, data_azione
ORDER BY user_id, data_azione;

4. Query con array: selezione e filtraggio

Quando abbiamo degli array, dobbiamo saperli estrarre e analizzare. Puoi usare il classico SELECT per ottenere un array:

SELECT tags FROM articoli WHERE id = 1;

Questo restituirà:

Ma che fare se vogliamo trovare un articolo che ha un certo tag, tipo PostgreSQL? Questo lo vedremo meglio nella prossima lezione, ma l’idea è semplice: gli array ti danno flessibilità e ti permettono di cercare valori dentro gli array.