Introduzione a PostgreSQL

PostgreSQL (spesso chiamato "Postgres") è il database relazionale open-source più avanzato al mondo. Supporta pienamente lo standard SQL, aggiunge tipi di dato avanzati (JSON, array, geometrie), funzioni finestra, CTE ricorsive, full-text search e molto altro.

A differenza di MySQL, PostgreSQL è progettato per la conformità agli standard e la correttezza dei dati: MVCC nativo, transazioni ACID vere, vincoli CHECK, FOREIGN KEY differiti e tipi di dato personalizzabili tramite estensioni (PostGIS, pgvector, TimescaleDB…).

Caratteristiche principali

Open-source e gratuito — licenza PostgreSQL (simile a MIT)
ACID compliant — transazioni sicure, nessuna perdita di dati
Tipi avanzati — JSON/JSONB, array, hstore, range, UUID, geometric
Estensibilità — funzioni in PL/pgSQL, PL/Python, PL/Perl; estensioni PostGIS, pgvector
Scalabilità — partitioning nativo, logical replication, pg_bouncer per il pooling
Full-text search — integrato, senza ElasticSearch per casi base
Window functions — analisi avanzata senza subquery complesse

PostgreSQL vs MySQL vs SQLite

Caratteristica	PostgreSQL	MySQL	SQLite
Licenza	Open-source (PostgreSQL)	GPL/Commercial	Public domain
ACID	Completo	Con InnoDB	Parziale
JSON nativo	JSONB (indicizzabile)	JSON	No
Window functions	Sì	Sì (5.7+)	Sì (3.25+)
Full-text search	Integrato	Integrato	Limitato
Ideale per	App enterprise, analytics	Web app generiche	App embedded, prototipazione

💡 Dove esercitarsi: usa neon.tech (PostgreSQL serverless gratuito), ElephantSQL o installa PostgreSQL localmente. Puoi anche usare DB Fiddle per query rapide online.

PostgreSQL — Introduzione

PostgreSQL nasce nel 1986 all'Università di Berkeley come successore di Ingres. Il nome completo era "POSTGRES", diventato poi "PostgreSQL" quando fu aggiunto il supporto SQL nel 1995. Oggi è mantenuto da un team globale di sviluppatori volontari.

Architettura di PostgreSQL

Processo postmaster — il processo padre che accetta le connessioni
Backend process — un processo separato per ogni connessione client
Shared buffers — cache condivisa in memoria (parametro shared_buffers)
WAL (Write-Ahead Log) — log delle transazioni per durabilità e replica
Background workers — autovacuum, checkpointer, wal writer, stats collector

Struttura di un database PostgreSQL

Gerarchia degli oggetti

-- PostgreSQL cluster
--   └── Database (es. "myapp")
--         └── Schema (es. "public", "auth", "analytics")
--               ├── Tables
--               ├── Views
--               ├── Functions
--               ├── Sequences
--               └── Indexes

-- Il search_path determina lo schema predefinito
SHOW search_path;           -- di solito "$user", public
SET search_path TO myschema, public;

Tipi di dato principali

Categoria	Tipi
Numerici	`INTEGER`, `BIGINT`, `NUMERIC(p,s)`, `REAL`, `DOUBLE PRECISION`
Testo	`VARCHAR(n)`, `TEXT`, `CHAR(n)`
Data/Ora	`DATE`, `TIME`, `TIMESTAMP`, `TIMESTAMPTZ`, `INTERVAL`
Booleano	`BOOLEAN` (true/false/null)
JSON	`JSON` (testo), `JSONB` (binario, indicizzabile)
Array	`INTEGER[]`, `TEXT[]`, qualsiasi tipo`[]`
UUID	`UUID` — identificatori univoci
Geometrici	`POINT`, `LINE`, `CIRCLE`, `POLYGON`
Speciali	`SERIAL`/`BIGSERIAL` (auto-increment), `MONEY`, `INET`, `CIDR`

💡 Preferisci TEXT a VARCHAR(n) in PostgreSQL: le performance sono identiche e non devi scegliere una lunghezza massima. Usa VARCHAR(n) solo se hai un requisito di business sulla lunghezza massima.

PostgreSQL — Installazione

PostgreSQL è disponibile per tutti i principali sistemi operativi. Puoi installarlo localmente, usarlo via Docker oppure affidarti a un servizio cloud come Neon, Supabase o Amazon RDS.

Ubuntu / Debian

Installa PostgreSQL su Ubuntu

# Aggiungi il repository ufficiale PostgreSQL
sudo apt install -y curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail \
  https://www.postgresql.org/media/keys/ACCC4CF8.asc
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] \
  https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" \
  > /etc/apt/sources.list.d/pgdg.list'

sudo apt update && sudo apt install -y postgresql postgresql-client

# Avvia il servizio
sudo systemctl start postgresql
sudo systemctl enable postgresql

# Verifica versione
psql --version

macOS (Homebrew)

Installa PostgreSQL su macOS

brew install postgresql@16
brew services start postgresql@16

# Aggiungi al PATH
echo 'export PATH="/opt/homebrew/opt/postgresql@16/bin:$PATH"' >> ~/.zshrc
source ~/.zshrc

psql --version

Docker (consigliato per sviluppo)

PostgreSQL con Docker

# Avvia un container PostgreSQL
docker run -d \
  --name postgres \
  -e POSTGRES_USER=admin \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=myapp \
  -p 5432:5432 \
  postgres:16

# Accedi alla shell psql
docker exec -it postgres psql -U admin -d myapp

# Con docker-compose (consigliato)
cat > docker-compose.yml << EOF
services:
  db:
    image: postgres:16
    environment:
      POSTGRES_USER: admin
      POSTGRES_PASSWORD: secret
      POSTGRES_DB: myapp
    ports: ["5432:5432"]
    volumes: [pgdata:/var/lib/postgresql/data]
volumes:
  pgdata:
EOF
docker compose up -d

Windows

Scarica l'installer da postgresql.org/download/windows
L'installer include PostgreSQL, pgAdmin 4 e Stack Builder
Scegli la porta (default 5432) e imposta la password per l'utente postgres
Dopo l'installazione usa pgAdmin 4 o psql dal menu Start

☁️ Alternativa cloud: Neon offre PostgreSQL serverless con 0.5 GB gratuiti. Supabase aggiunge API REST, autenticazione e Storage sopra PostgreSQL. Entrambi hanno tier gratuiti ottimi per imparare.

PostgreSQL — Per Iniziare

Una volta installato PostgreSQL, il punto d'ingresso principale è psql — la shell interattiva a riga di comando. Da qui puoi eseguire query, gestire database, utenti e configurare il server.

Accesso con psql

Comandi base psql

# Accedi come utente postgres (superuser)
sudo -u postgres psql

# Accedi a un database specifico
psql -U admin -d myapp -h localhost -p 5432

# Da dentro psql — meta-comandi (iniziano con \)
\l              -- lista tutti i database
\c myapp        -- connettiti a un database
\dt             -- lista le tabelle dello schema corrente
\dt *.*         -- lista tutte le tabelle di tutti gli schemi
\d nome_tabella -- struttura di una tabella
\du             -- lista utenti/ruoli
\dn             -- lista schemi
\df             -- lista funzioni
\i file.sql     -- esegui un file SQL
	iming         -- attiva/disattiva il cronometro delle query
\q              -- esci

Crea il tuo primo database e utente

Setup database e utente

-- Connesso come superuser (postgres)

-- Crea un utente con password
CREATE USER devuser WITH PASSWORD 'mypassword';

-- Crea un database
CREATE DATABASE myapp OWNER devuser;

-- Assegna tutti i privilegi
GRANT ALL PRIVILEGES ON DATABASE myapp TO devuser;

-- Connettiti al nuovo database
\c myapp devuser

-- Verifica
SELECT current_user, current_database();

Eseguire query SQL

Prima query PostgreSQL

-- Crea uno schema
CREATE SCHEMA IF NOT EXISTS negozio;
SET search_path TO negozio, public;

-- Crea una tabella di prova
CREATE TABLE clienti (
  id    SERIAL PRIMARY KEY,
  nome  TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL,
  creato TIMESTAMPTZ DEFAULT NOW()
);

-- Inserisci dati
INSERT INTO clienti (nome, email) VALUES
  ('Mario Rossi',  'mario@example.com'),
  ('Anna Bianchi', 'anna@example.com');

-- Leggi i dati
SELECT * FROM clienti;

Connection string

URI di connessione PostgreSQL

-- Formato URI standard
postgresql://utente:password@host:porta/database

-- Esempi
postgresql://admin:secret@localhost:5432/myapp
postgresql://admin:secret@db.example.com/prod?sslmode=require

-- Con psql
psql "postgresql://admin:secret@localhost/myapp"

-- Variabili d'ambiente (consigliato per sicurezza)
export PGHOST=localhost
export PGPORT=5432
export PGDATABASE=myapp
export PGUSER=admin
export PGPASSWORD=secret
psql  # usa automaticamente le variabili

pgAdmin 4

pgAdmin 4 è il tool grafico ufficiale per gestire PostgreSQL. Permette di esplorare database, eseguire query con syntax highlighting, gestire utenti, visualizzare piani di esecuzione e monitorare il server — tutto da browser.

Installazione pgAdmin 4

Windows/macOS: incluso nell'installer ufficiale PostgreSQL
Ubuntu: sudo apt install pgadmin4 (oppure scarica da pgadmin.org)
Docker: docker run -p 80:80 -e PGADMIN_DEFAULT_EMAIL=admin@test.com -e PGADMIN_DEFAULT_PASSWORD=admin dpage/pgadmin4
Cloud: pgAdmin è integrato in Supabase, Railway, Render e altri servizi

Funzionalità principali

Query Tool — editor SQL con autocompletamento, evidenziazione sintassi e visualizzazione risultati
Schema Browser — esplora tabelle, indici, funzioni, trigger e viste graficamente
EXPLAIN Visualizer — visualizza il piano di esecuzione delle query con grafico interattivo
Backup/Restore — interfaccia grafica per pg_dump e pg_restore
Server Monitor — statistiche su connessioni, lock, blocchi, utilizzo CPU/memoria
ERD (Entity Relationship Diagram) — genera diagrammi ER dalle tabelle esistenti

Connessione a PostgreSQL in pgAdmin

/* In pgAdmin 4:
   1. File → Add New Server (o click destro su "Servers")
   2. Scheda "General": dai un nome al server (es. "Local Dev")
   3. Scheda "Connection":
      Host:     localhost
      Port:     5432
      Database: myapp
      Username: admin
      Password: secret
   4. Click "Save" — pgAdmin si connette automaticamente

   Alternativa rapida: usa la "Connection String" nella scheda Connection:
   postgresql://admin:secret@localhost:5432/myapp
*/

Query Tool — scorciatoie

Scorciatoia	Azione
`F5`	Esegui tutta la query
`F9`	Esegui solo la query selezionata
`Shift+Ctrl+E`	Mostra il piano EXPLAIN
`Ctrl+Space`	Autocompletamento
`Ctrl+/`	Commenta/decommenta riga
`F7`	Apri la finestra EXPLAIN grafica

💡 Alternativa leggera: DBeaver supporta PostgreSQL (e decine di altri DB) ed è gratuito. TablePlus è ottimo su macOS. DataGrip (a pagamento, con trial) è il più completo per sviluppatori professionali.

PostgreSQL — CREATE TABLE

Il comando CREATE TABLE definisce una nuova tabella specificando nome, colonne, tipi di dato e vincoli. In PostgreSQL è uno dei comandi DDL (Data Definition Language) più ricchi di opzioni.

CREATE TABLE base

CREATE TABLE prodotti (
  id          SERIAL PRIMARY KEY,
  nome        VARCHAR(200)   NOT NULL,
  prezzo      NUMERIC(10,2)  NOT NULL CHECK (prezzo >= 0),
  stock       INTEGER        NOT NULL DEFAULT 0,
  categoria   TEXT,
  disponibile BOOLEAN        DEFAULT TRUE,
  creato      TIMESTAMPTZ    DEFAULT NOW(),
  aggiornato  TIMESTAMPTZ    DEFAULT NOW()
);

Vincoli principali

Vincolo	Descrizione
`PRIMARY KEY`	Identifica univocamente ogni riga
`NOT NULL`	Il campo non può essere NULL
`UNIQUE`	Nessun duplicato (permette un solo NULL)
`DEFAULT valore`	Valore predefinito se non specificato
`CHECK (cond)`	La condizione deve essere vera per ogni riga
`REFERENCES`	Chiave esterna (FOREIGN KEY)

Tabella con chiave esterna e vincoli avanzati

-- Tabella categorie
CREATE TABLE categorie (
  id   SERIAL PRIMARY KEY,
  nome TEXT UNIQUE NOT NULL
);

-- Tabella prodotti con FK
CREATE TABLE prodotti (
  id           SERIAL PRIMARY KEY,
  nome         TEXT         NOT NULL,
  prezzo       NUMERIC(10,2) NOT NULL CHECK (prezzo > 0),
  stock        INTEGER       NOT NULL DEFAULT 0 CHECK (stock >= 0),
  categoria_id INTEGER       REFERENCES categorie(id) ON DELETE SET NULL,
  ean          CHAR(13)      UNIQUE,
  peso_kg      NUMERIC(8,3),
  CONSTRAINT chk_peso CHECK (peso_kg IS NULL OR peso_kg > 0)
);

-- Verifica la struttura
\d prodotti

Tabella con chiave primaria composta

-- Tabella molti-a-molti
CREATE TABLE ordine_prodotti (
  ordine_id   INTEGER REFERENCES ordini(id)   ON DELETE CASCADE,
  prodotto_id INTEGER REFERENCES prodotti(id) ON DELETE RESTRICT,
  quantita    INTEGER NOT NULL CHECK (quantita > 0),
  prezzo_unit NUMERIC(10,2) NOT NULL,
  PRIMARY KEY (ordine_id, prodotto_id)
);

💡 SERIAL vs GENERATED ALWAYS AS IDENTITY: SERIAL è la versione storica (crea una sequence implicita). Da PostgreSQL 10+ usa la sintassi standard SQL: id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY — più portabile e senza effetti collaterali.

PostgreSQL — INSERT INTO

INSERT INTO aggiunge nuovi record a una tabella. PostgreSQL estende lo standard SQL con la clausola RETURNING che restituisce i valori delle righe inserite, molto utile per ottenere gli ID generati.

INSERT base

-- Inserisce una riga (specifica tutte le colonne)
INSERT INTO clienti (nome, email, citta)
VALUES ('Mario Rossi', 'mario@example.com', 'Roma');

-- Inserisce più righe in una sola istruzione
INSERT INTO clienti (nome, email, citta) VALUES
  ('Anna Bianchi',  'anna@example.com',   'Milano'),
  ('Luca Ferrari',  'luca@example.com',   'Torino'),
  ('Sofia Esposito','sofia@example.com',  'Napoli');

INSERT ... RETURNING

-- Restituisce l''id generato
INSERT INTO prodotti (nome, prezzo, stock)
VALUES ('Laptop Pro', 1299.99, 10)
RETURNING id;

-- Restituisce più colonne
INSERT INTO ordini (cliente_id, totale, stato)
VALUES (42, 599.50, 'pending')
RETURNING id, creato, numero_ordine;

-- Restituisce tutto
INSERT INTO categorie (nome)
VALUES ('Elettronica')
RETURNING *;

INSERT ... ON CONFLICT (upsert)

-- ON CONFLICT DO NOTHING — ignora i duplicati
INSERT INTO tag (nome, slug)
VALUES ('javascript', 'javascript')
ON CONFLICT (slug) DO NOTHING;

-- ON CONFLICT DO UPDATE — aggiorna se esiste (UPSERT)
INSERT INTO prodotti (ean, nome, prezzo, stock)
VALUES ('1234567890123', 'Mouse', 29.99, 100)
ON CONFLICT (ean)
DO UPDATE SET
  prezzo = EXCLUDED.prezzo,
  stock  = prodotti.stock + EXCLUDED.stock,
  aggiornato = NOW();

-- EXCLUDED riferisce ai valori che si voleva inserire

INSERT da SELECT

-- Copia dati da un''altra tabella
INSERT INTO archivio_ordini (ordine_id, cliente_id, totale, data_archiviazione)
SELECT id, cliente_id, totale, NOW()
FROM   ordini
WHERE  stato = 'completato'
  AND  data_creazione < NOW() - INTERVAL '1 year';

PostgreSQL — Fetch Data (SELECT)

SELECT è il comando per leggere i dati. PostgreSQL offre un set ricchissimo di clausole per filtrare, ordinare, raggruppare, limitare e trasformare i risultati.

SELECT base

-- Tutte le colonne
SELECT * FROM prodotti;

-- Colonne specifiche
SELECT nome, prezzo, stock FROM prodotti;

-- Con espressioni e alias
SELECT
  nome,
  prezzo,
  prezzo * 1.22  AS prezzo_iva,
  stock > 0      AS disponibile
FROM prodotti;

-- Valori calcolati senza tabella
SELECT 2 + 3 AS somma, NOW() AS ora, VERSION() AS versione;

WHERE, ORDER BY, LIMIT, OFFSET

-- Filtra con WHERE
SELECT nome, prezzo
FROM   prodotti
WHERE  prezzo BETWEEN 50 AND 500
  AND  disponibile = TRUE
ORDER BY prezzo DESC;

-- LIMIT e OFFSET per la paginazione
SELECT * FROM prodotti
ORDER BY id
LIMIT  10
OFFSET 20;   -- pagina 3 (0-based, da elemento 20)

-- FETCH FIRST (sintassi SQL standard)
SELECT * FROM prodotti
ORDER BY creato DESC
FETCH FIRST 5 ROWS ONLY;

Funzioni di stringa e testo

SELECT
  UPPER(nome)          AS nome_maiuscolo,
  LENGTH(email)        AS lunghezza_email,
  SUBSTRING(nome, 1, 3) AS prime_tre_lettere,
  TRIM(BOTH ' ' FROM descrizione) AS descrizione_pulita,
  CONCAT(nome, ' - ', citta) AS nome_citta,
  nome || ' (' || COALESCE(citta, 'N/D') || ')' AS etichetta
FROM clienti;

Funzioni di data e ora

SELECT
  NOW()                        AS adesso,
  CURRENT_DATE                 AS oggi,
  DATE_TRUNC(''month'', NOW())   AS inizio_mese,
  NOW() - INTERVAL ''30 days''   AS un_mese_fa,
  EXTRACT(YEAR  FROM creato)   AS anno,
  EXTRACT(MONTH FROM creato)   AS mese,
  AGE(data_nascita)            AS eta,
  TO_CHAR(NOW(), ''DD/MM/YYYY HH24:MI'') AS formattato
FROM clienti;

PostgreSQL — ADD COLUMN

ALTER TABLE ... ADD COLUMN aggiunge una o più colonne a una tabella esistente senza perdere i dati presenti. La nuova colonna viene aggiunta in fondo (PostgreSQL non supporta l'inserimento in posizione specifica senza ricreare la tabella).

ADD COLUMN

-- Aggiungi una colonna
ALTER TABLE clienti ADD COLUMN telefono TEXT;

-- Con DEFAULT (la nuova colonna avrà quel valore per le righe esistenti)
ALTER TABLE prodotti ADD COLUMN iva_percent NUMERIC(5,2) DEFAULT 22.00;

-- NOT NULL richiede un DEFAULT (per le righe esistenti)
ALTER TABLE ordini ADD COLUMN note TEXT NOT NULL DEFAULT '';

-- Più colonne in un''unico ALTER TABLE
ALTER TABLE utenti
  ADD COLUMN avatar_url TEXT,
  ADD COLUMN bio        TEXT,
  ADD COLUMN verificato BOOLEAN NOT NULL DEFAULT FALSE;

ADD COLUMN con vincoli

-- Colonna con CHECK
ALTER TABLE prodotti
  ADD COLUMN sconto_percent NUMERIC(5,2)
  CHECK (sconto_percent BETWEEN 0 AND 100);

-- Colonna con FOREIGN KEY
ALTER TABLE ordini
  ADD COLUMN spedizione_id INTEGER
  REFERENCES spedizioni(id) ON DELETE SET NULL;

-- Colonna GENERATED (calcolata automaticamente)
ALTER TABLE prodotti
  ADD COLUMN prezzo_scontato NUMERIC(10,2)
  GENERATED ALWAYS AS (prezzo * (1 - COALESCE(sconto_percent,0)/100)) STORED;

📌 In PostgreSQL, aggiungere una colonna con DEFAULT (da versione 11+) è un'operazione istantanea anche su tabelle enormi: il valore di default viene memorizzato nel catalogo e applicato solo alla lettura, senza riscrivere l'intera tabella.

PostgreSQL — UPDATE

UPDATE modifica i valori in una o più righe esistenti. PostgreSQL estende lo standard con UPDATE ... FROM (simile a un JOIN) e RETURNING per ottenere le righe modificate.

UPDATE base

-- Aggiorna tutte le righe (ATTENZIONE: senza WHERE aggiorna tutto!)
UPDATE prodotti SET disponibile = FALSE;

-- Con WHERE
UPDATE prodotti
SET    prezzo = prezzo * 1.10,
       aggiornato = NOW()
WHERE  categoria_id = 3;

-- Aggiorna con RETURNING
UPDATE clienti
SET    citta = ''Torino''
WHERE  id = 42
RETURNING id, nome, citta;

UPDATE ... FROM (join tra tabelle)

-- Aggiorna usando dati da un''altra tabella
UPDATE prodotti AS p
SET    prezzo = pc.nuovo_prezzo,
       aggiornato = NOW()
FROM   prezzi_aggiornati AS pc
WHERE  p.ean = pc.ean;

-- Esempio pratico: aggiorna lo stock dopo un ordine
UPDATE prodotti AS p
SET    stock = p.stock - op.quantita
FROM   ordine_prodotti AS op
WHERE  p.id = op.prodotto_id
  AND  op.ordine_id = 1001;

UPDATE con subquery

-- Aggiorna basandosi su una subquery
UPDATE utenti
SET    livello = ''premium''
WHERE  id IN (
  SELECT DISTINCT cliente_id
  FROM   ordini
  WHERE  totale > 1000
    AND  data_creazione >= NOW() - INTERVAL ''6 months''
);

⚠️ Un UPDATE senza WHERE modifica tutte le righe della tabella. Prima di eseguire un UPDATE importante, lancia sempre un SELECT con le stesse condizioni per verificare quante righe verranno modificate.

PostgreSQL — ALTER COLUMN

ALTER TABLE ... ALTER COLUMN permette di modificare le proprietà di una colonna esistente: tipo di dato, valore di default, vincolo NOT NULL e altro.

Cambia il tipo di dato

-- Cambia il tipo (PostgreSQL usa SET DATA TYPE)
ALTER TABLE prodotti
  ALTER COLUMN prezzo SET DATA TYPE NUMERIC(12,2);

-- Con conversione esplicita (USING)
ALTER TABLE utenti
  ALTER COLUMN eta SET DATA TYPE INTEGER
  USING eta::INTEGER;   -- converte il vecchio valore

-- Da TEXT a TIMESTAMPTZ
ALTER TABLE log
  ALTER COLUMN timestamp_str SET DATA TYPE TIMESTAMPTZ
  USING timestamp_str::TIMESTAMPTZ;

SET DEFAULT, DROP DEFAULT, NOT NULL

-- Imposta un valore di default
ALTER TABLE prodotti
  ALTER COLUMN stock SET DEFAULT 0;

-- Rimuove il valore di default
ALTER TABLE prodotti
  ALTER COLUMN note DROP DEFAULT;

-- Rende la colonna NOT NULL
-- (richiede che nessun valore sia NULL)
ALTER TABLE clienti
  ALTER COLUMN email SET NOT NULL;

-- Rimuove il NOT NULL
ALTER TABLE clienti
  ALTER COLUMN telefono DROP NOT NULL;

Rinomina colonna e tabella

-- Rinomina una colonna
ALTER TABLE clienti RENAME COLUMN citta TO city;

-- Rinomina la tabella
ALTER TABLE clienti RENAME TO customers;

-- Rinomina il vincolo
ALTER TABLE prodotti
  RENAME CONSTRAINT chk_peso TO chk_peso_positivo;

PostgreSQL — DROP COLUMN

ALTER TABLE ... DROP COLUMN rimuove una colonna da una tabella. Attenzione: l'operazione è irreversibile e rimuove anche tutti gli indici, i default e i vincoli associati a quella colonna.

DROP COLUMN

-- Rimuove una colonna
ALTER TABLE clienti DROP COLUMN fax;

-- Con IF EXISTS (nessun errore se la colonna non esiste)
ALTER TABLE prodotti DROP COLUMN IF EXISTS campo_obsoleto;

-- Rimuove più colonne in una sola istruzione
ALTER TABLE utenti
  DROP COLUMN vecchio_campo,
  DROP COLUMN altro_campo_inutile;

CASCADE — rimuovi le dipendenze

-- Se la colonna è referenziata da altre tabelle o viste,
-- DROP COLUMN normale darà errore. Usa CASCADE:
ALTER TABLE categorie DROP COLUMN codice CASCADE;
-- CASCADE elimina automaticamente:
--   - Viste che usano questa colonna
--   - Regole e trigger dipendenti
--   - Indici su questa colonna (vengono eliminati in ogni caso)

-- Verifica le dipendenze prima di droppare
SELECT dependent_ns.nspname,
       dependent_view.relname
FROM   pg_depend
JOIN   pg_rewrite ON pg_depend.objid = pg_rewrite.oid
JOIN   pg_class AS dependent_view ON pg_rewrite.ev_class = dependent_view.oid
JOIN   pg_class AS source_table ON pg_depend.refobjid = source_table.oid
JOIN   pg_attribute ON pg_depend.refobjsubid = pg_attribute.attnum
  AND  pg_attribute.attrelid = pg_depend.refobjid
JOIN   pg_namespace AS dependent_ns ON dependent_view.relnamespace = dependent_ns.oid
WHERE  source_table.relname = 'nome_tabella'
  AND  pg_attribute.attname = 'nome_colonna';

⚠️ DROP COLUMN non recupera immediatamente lo spazio disco — PostgreSQL marca la colonna come eliminata ma il dato fisico rimane fino al prossimo VACUUM FULL. Per tabelle grandi, pianifica la manutenzione.

PostgreSQL — DELETE

DELETE FROM rimuove righe da una tabella. Come UPDATE, PostgreSQL supporta DELETE ... USING (join) e RETURNING per ottenere le righe eliminate.

DELETE base

-- Elimina righe specifiche
DELETE FROM sessioni WHERE scaduta = TRUE;

-- Elimina con condizione su data
DELETE FROM log
WHERE timestamp < NOW() - INTERVAL ''90 days'';

-- RETURNING — restituisce le righe eliminate
DELETE FROM carrello
WHERE utente_id = 42 AND scaduto = TRUE
RETURNING prodotto_id, quantita;

DELETE ... USING (join)

-- Elimina prodotti senza ordini (usando USING)
DELETE FROM prodotti AS p
USING  categorie AS c
WHERE  p.categoria_id = c.id
  AND  c.nome = ''Archivio'';

-- Elimina basandosi su subquery
DELETE FROM utenti
WHERE id IN (
  SELECT id FROM utenti
  WHERE  ultimo_accesso < NOW() - INTERVAL ''2 years''
    AND  num_ordini = 0
);

TRUNCATE — svuota velocemente

-- TRUNCATE è molto più veloce di DELETE per svuotare una tabella
TRUNCATE prodotti;

-- Con RESTART IDENTITY (azzera i SERIAL/sequence)
TRUNCATE ordini RESTART IDENTITY;

-- CASCADE per svuotare anche le tabelle dipendenti
TRUNCATE clienti CASCADE;

-- Differenze DELETE vs TRUNCATE:
-- DELETE: riga per riga, può usare WHERE, attiva trigger, lento su tante righe
-- TRUNCATE: svuota tutto, ignora WHERE, più veloce, resetta sequence

PostgreSQL — DROP TABLE

DROP TABLE rimuove una tabella e tutti i suoi dati in modo irreversibile. Usa IF EXISTS per evitare errori se la tabella non esiste, e CASCADE per gestire le dipendenze.

DROP TABLE

-- Elimina una tabella
DROP TABLE temp_import;

-- Con IF EXISTS (sicuro da eseguire in script)
DROP TABLE IF EXISTS temp_calcoli;

-- CASCADE — elimina anche tabelle dipendenti (FK) e viste
DROP TABLE clienti CASCADE;
-- Attenzione: CASCADE elimina TUTTO ciò che dipende dalla tabella

-- Elimina più tabelle in una sola istruzione
DROP TABLE IF EXISTS
  temp_a,
  temp_b,
  temp_c;

DROP SCHEMA e DATABASE

-- Elimina uno schema e tutto il suo contenuto
DROP SCHEMA analytics CASCADE;

-- Crea uno schema solo se non esiste
CREATE SCHEMA IF NOT EXISTS analytics;

-- Elimina il database (devi essere connesso a un altro DB)
\c postgres          -- connettiti al db "postgres"
DROP DATABASE myapp; -- ora puoi eliminarlo

-- Con IF EXISTS
DROP DATABASE IF EXISTS test_db;

📌 Prima di fare DROP TABLE in produzione, crea sempre un backup: pg_dump -t nome_tabella mydb > backup_tabella.sql. Puoi anche usare CREATE TABLE backup_nome AS SELECT * FROM nome_tabella; per un backup rapido nella stessa istanza.

Database Demo — Northwind

Per tutti gli esempi pratici useremo un database demo ispirato al classico Northwind di Microsoft, adattato per PostgreSQL. Contiene tabelle di clienti, prodotti, ordini e spedizioni.

Schema Northwind per PostgreSQL

-- Crea il database demo
CREATE TABLE categorie (
  id   SERIAL PRIMARY KEY,
  nome TEXT UNIQUE NOT NULL
);

CREATE TABLE fornitori (
  id    SERIAL PRIMARY KEY,
  nome  TEXT NOT NULL,
  paese TEXT
);

CREATE TABLE prodotti (
  id           SERIAL PRIMARY KEY,
  nome         TEXT NOT NULL,
  fornitore_id INTEGER REFERENCES fornitori(id),
  categoria_id INTEGER REFERENCES categorie(id),
  prezzo       NUMERIC(10,2) NOT NULL DEFAULT 0,
  stock        INTEGER NOT NULL DEFAULT 0,
  discontinuato BOOLEAN DEFAULT FALSE
);

CREATE TABLE clienti (
  id     SERIAL PRIMARY KEY,
  nome   TEXT NOT NULL,
  email  TEXT UNIQUE,
  citta  TEXT,
  paese  TEXT DEFAULT ''Italia''
);

CREATE TABLE ordini (
  id          SERIAL PRIMARY KEY,
  cliente_id  INTEGER REFERENCES clienti(id),
  data_ordine DATE NOT NULL DEFAULT CURRENT_DATE,
  totale      NUMERIC(12,2),
  stato       TEXT DEFAULT ''in attesa''
    CHECK (stato IN (''in attesa'',''confermato'',''spedito'',''consegnato'',''annullato''))
);

CREATE TABLE ordine_dettagli (
  ordine_id   INTEGER REFERENCES ordini(id) ON DELETE CASCADE,
  prodotto_id INTEGER REFERENCES prodotti(id),
  quantita    INTEGER NOT NULL CHECK (quantita > 0),
  prezzo_unit NUMERIC(10,2) NOT NULL,
  sconto      NUMERIC(5,2) DEFAULT 0,
  PRIMARY KEY (ordine_id, prodotto_id)
);

Popola il database demo

-- Categorie
INSERT INTO categorie (nome) VALUES
  (''Elettronica''),('Abbigliamento'),('Alimentari'),('Libri'),('Sport'');

-- Fornitori
INSERT INTO fornitori (nome, paese) VALUES
  (''TechCorp'',''Italia''),(''FashionHub'',''Francia'),
  (''FoodPlus'',''Spagna''),(''BookWorld'',''Italia'');

-- Prodotti
INSERT INTO prodotti (nome, fornitore_id, categoria_id, prezzo, stock) VALUES
  (''Laptop Pro'',    1, 1, 1299.99, 15),
  (''Mouse wireless'',1, 1,   29.99, 80),
  (''T-Shirt XL'',   2, 2,   19.99, 120),
  (''Pasta integrale'',3,3,    2.49,300),
  (''SQL Handbook'', 4, 4,   39.99, 40),
  (''Scarpe running'',2,5,   89.99, 55);

-- Clienti
INSERT INTO clienti (nome, email, citta) VALUES
  (''Mario Rossi'',  ''mario@ex.com'',''Roma''),
  (''Anna Bianchi'', ''anna@ex.com'', ''Milano''),
  (''Luca Ferrari'', ''luca@ex.com'', ''Torino'');

-- Ordini
INSERT INTO ordini (cliente_id, data_ordine, totale, stato) VALUES
  (1, ''2024-01-15'', 1329.98, ''consegnato''),
  (2, ''2024-02-20'', 109.98,  ''spedito''),
  (3, ''2024-03-05'', 42.48,   ''in attesa'');

PostgreSQL — Operatori

PostgreSQL offre un set ricco di operatori per confrontare valori, eseguire operazioni aritmetiche, lavorare con stringhe, pattern e tipi speciali.

Operatori di confronto

Operatore	Descrizione
`=`	Uguale
`<>` o `!=`	Diverso
`<`	Minore
`>`	Maggiore
`<=`	Minore o uguale
`>=`	Maggiore o uguale
`IS NULL`	Il valore è NULL
`IS NOT NULL`	Il valore non è NULL
`IS DISTINCT FROM`	Diverso (gestisce NULL come valore)

Operatori in pratica

-- Confronto NULL-safe
SELECT * FROM prodotti WHERE categoria_id IS NULL;
SELECT * FROM prodotti WHERE discontinuato IS NOT TRUE;

-- IS DISTINCT FROM (diverso da <> perché gestisce NULL)
SELECT * FROM a WHERE col IS DISTINCT FROM col2;

-- Operatori logici
SELECT * FROM prodotti
WHERE  prezzo > 50 AND stock > 0 AND NOT discontinuato;

-- BETWEEN (inclusivo)
SELECT * FROM ordini
WHERE  data_ordine BETWEEN ''2024-01-01'' AND ''2024-12-31'';

Operatori stringa

Operatori su stringhe

-- Concatenazione
SELECT ''Hello'' || '' '' || ''World'' AS saluto;

-- LIKE (% = qualsiasi sequenza, _ = un carattere)
SELECT * FROM prodotti WHERE nome LIKE ''Laptop%'';
SELECT * FROM clienti  WHERE nome LIKE ''_aria%'';

-- ILIKE (case-insensitive)
SELECT * FROM prodotti WHERE nome ILIKE ''%pro%'';

-- SIMILAR TO (regex semplificato)
SELECT * FROM codici WHERE valore SIMILAR TO ''[A-Z]{2}[0-9]{4}'';

-- ~ (regex POSIX, case-sensitive)
SELECT * FROM email_list WHERE email ~ ''^[a-z]+@'';
SELECT * FROM email_list WHERE email ~* ''^mario'';  -- i = case-insensitive

Operatori numerici e speciali

Operatori numerici e JSON

-- Aritmetica
SELECT 17 / 5,       -- 3 (divisione intera)
       17.0 / 5,     -- 3.4 (decimale)
       17 % 5,       -- 2 (modulo)
       2 ^ 10,       -- 1024 (potenza)
       |/ 25,        -- 5 (radice quadrata)
       @ -15;        -- 15 (valore assoluto)

-- Operatori JSON/JSONB
SELECT dati -> ''nome''     AS nome_json,    -- estrae come JSON
       dati ->> ''nome''    AS nome_testo,   -- estrae come TEXT
       dati -> ''indirizzo'' ->> ''citta'' AS citta  -- accesso annidato
FROM utenti;

-- Operatori array
SELECT ARRAY[1,2,3] @> ARRAY[2,3] AS contiene,    -- TRUE
       ARRAY[1,2] && ARRAY[2,3]   AS sovrappone;   -- TRUE

PostgreSQL — SELECT

SELECT è il comando più usato in PostgreSQL. Supporta espressioni, funzioni, alias, valori calcolati, e si combina con molte clausole per costruire query complesse.

SELECT avanzato

-- SELECT con espressioni e funzioni
SELECT
  id,
  UPPER(nome)                          AS nome_upper,
  ROUND(prezzo, 0)                     AS prezzo_arrotondato,
  CASE
    WHEN prezzo < 50  THEN ''Economico''
    WHEN prezzo < 200 THEN ''Medio''
    ELSE ''Premium''
  END                                  AS fascia,
  COALESCE(descrizione, ''N/D'')         AS descrizione,
  NOW()::DATE                          AS oggi
FROM prodotti
WHERE NOT discontinuato
ORDER BY prezzo DESC;

SELECT DISTINCT su più colonne

-- Combinazioni uniche di paese e citta
SELECT DISTINCT paese, citta
FROM clienti
ORDER BY paese, citta;

-- COUNT DISTINCT
SELECT COUNT(DISTINCT categoria_id) AS num_categorie_usate
FROM prodotti;

Subquery in SELECT

-- Subquery scalare (restituisce un valore)
SELECT
  nome,
  prezzo,
  (SELECT AVG(prezzo) FROM prodotti) AS media_prezzi,
  prezzo - (SELECT AVG(prezzo) FROM prodotti) AS diff_dalla_media
FROM prodotti;

-- EXISTS come condizione
SELECT nome
FROM clienti c
WHERE EXISTS (
  SELECT 1 FROM ordini o
  WHERE o.cliente_id = c.id AND o.totale > 500
);

PostgreSQL — SELECT DISTINCT

SELECT DISTINCT elimina i duplicati dai risultati, restituendo solo le righe uniche. Si può applicare a una o più colonne.

SELECT DISTINCT

-- Valori unici di una colonna
SELECT DISTINCT paese FROM clienti ORDER BY paese;

-- Combinazioni uniche di più colonne
SELECT DISTINCT paese, citta FROM clienti ORDER BY paese, citta;

-- DISTINCT ON (unico PostgreSQL) — la prima riga per ogni gruppo
SELECT DISTINCT ON (cliente_id)
  cliente_id, data_ordine, totale
FROM ordini
ORDER BY cliente_id, data_ordine DESC;
-- Restituisce l''ultimo ordine di ogni cliente

DISTINCT vs GROUP BY

-- Questi due producono lo stesso risultato:

-- Con DISTINCT
SELECT DISTINCT categoria_id FROM prodotti ORDER BY categoria_id;

-- Con GROUP BY (più flessibile — puoi aggiungere aggregazioni)
SELECT categoria_id, COUNT(*) AS num_prodotti
FROM   prodotti
GROUP BY categoria_id
ORDER BY categoria_id;

💡 DISTINCT ON è un'estensione PostgreSQL molto utile: restituisce la prima riga per ogni gruppo (determinato dall'ORDER BY). Equivale a un GROUP BY con FIRST_VALUE() ma è molto più leggibile.

PostgreSQL — WHERE

La clausola WHERE filtra le righe restituite da SELECT, UPDATE o DELETE. Supporta qualsiasi espressione booleana, incluse subquery, funzioni e operatori speciali.

WHERE con operatori vari

-- Confronto semplice
SELECT * FROM prodotti WHERE prezzo > 100;

-- Più condizioni
SELECT * FROM ordini
WHERE  stato = ''spedito''
  AND  data_ordine >= ''2024-01-01''
  AND  totale > 200;

-- OR
SELECT * FROM clienti
WHERE  citta = ''Roma'' OR citta = ''Milano'';

-- IN (equivalente a più OR)
SELECT * FROM clienti
WHERE  citta IN (''Roma'', ''Milano'', ''Torino'');

-- NOT IN
SELECT * FROM prodotti
WHERE  categoria_id NOT IN (3, 5);

-- BETWEEN (inclusivo)
SELECT * FROM prodotti
WHERE  prezzo BETWEEN 10 AND 100;

WHERE con NULL e LIKE

-- Filtra NULL
SELECT * FROM clienti WHERE telefono IS NULL;
SELECT * FROM prodotti WHERE descrizione IS NOT NULL;

-- LIKE — cerca il pattern
SELECT * FROM prodotti WHERE nome LIKE ''%wireless%'';
SELECT * FROM clienti  WHERE email ILIKE ''%gmail.com'';

-- ANY con array
SELECT * FROM prodotti
WHERE  id = ANY(ARRAY[1, 3, 7, 12]);

-- Subquery nel WHERE
SELECT * FROM clienti
WHERE  id IN (
  SELECT DISTINCT cliente_id FROM ordini
  WHERE  totale > 1000
);

PostgreSQL — ORDER BY

ORDER BY definisce l'ordinamento dei risultati. PostgreSQL permette di ordinare per colonne, espressioni, alias, numero di colonna e di gestire i valori NULL in modo esplicito.

ORDER BY

-- Ascendente (default)
SELECT * FROM prodotti ORDER BY nome;
SELECT * FROM prodotti ORDER BY prezzo ASC;

-- Decrescente
SELECT * FROM prodotti ORDER BY prezzo DESC;

-- Più colonne
SELECT * FROM prodotti ORDER BY categoria_id ASC, prezzo DESC;

-- Per alias definito nel SELECT
SELECT nome, prezzo * 1.22 AS prezzo_iva
FROM   prodotti
ORDER BY prezzo_iva DESC;

-- Per numero di colonna (sconsigliato, ma supportato)
SELECT nome, prezzo FROM prodotti ORDER BY 2 DESC;

NULLS FIRST / NULLS LAST

-- Default: ASC mette NULL in fondo, DESC li mette in testa

-- Metti i NULL sempre in fondo
SELECT * FROM prodotti ORDER BY fornitore_id ASC NULLS LAST;

-- Metti i NULL sempre in testa
SELECT * FROM prodotti ORDER BY fornitore_id DESC NULLS FIRST;

ORDER BY con espressioni

-- Ordina per la lunghezza del nome
SELECT nome FROM clienti ORDER BY LENGTH(nome);

-- Ordina per anno poi per mese
SELECT * FROM ordini
ORDER BY EXTRACT(YEAR  FROM data_ordine) DESC,
         EXTRACT(MONTH FROM data_ordine) DESC;

-- Ordinamento case-insensitive
SELECT * FROM prodotti ORDER BY LOWER(nome);

PostgreSQL — LIMIT

LIMIT restringe il numero di righe restituite. Combinato con OFFSET permette la paginazione. PostgreSQL supporta anche la sintassi SQL standard FETCH FIRST.

LIMIT e OFFSET

-- Prendi solo le prime 10 righe
SELECT * FROM prodotti ORDER BY prezzo DESC LIMIT 10;

-- Paginazione: pagina 3, 10 elementi per pagina
SELECT * FROM prodotti
ORDER BY id
LIMIT  10
OFFSET 20;   -- salta le prime 20 righe (pagine 1 e 2)

-- Calcolo pagina: OFFSET = (pagina - 1) * per_pagina
-- Pagina 1: LIMIT 10 OFFSET 0
-- Pagina 2: LIMIT 10 OFFSET 10
-- Pagina 3: LIMIT 10 OFFSET 20

FETCH FIRST (SQL standard)

-- Equivalente a LIMIT
SELECT * FROM prodotti
ORDER BY prezzo DESC
FETCH FIRST 5 ROWS ONLY;

-- Con OFFSET
SELECT * FROM prodotti
ORDER BY id
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;

⚡ Paginazione efficiente su grandi dataset: invece di OFFSET elevati (lento perché PostgreSQL salta comunque le righe), usa il keyset pagination (cursor-based): WHERE id > ultimo_id_visto ORDER BY id LIMIT 10. È O(log n) invece di O(n).

PostgreSQL — MIN e MAX

MIN() e MAX() sono funzioni aggregate che restituiscono rispettivamente il valore minimo e massimo di una colonna. Funzionano su numeri, stringhe (ordine alfabetico) e date.

MIN e MAX

-- Prezzo minimo e massimo
SELECT MIN(prezzo) AS prezzo_min,
       MAX(prezzo) AS prezzo_max
FROM   prodotti
WHERE  NOT discontinuato;

-- Su date
SELECT MIN(data_ordine) AS primo_ordine,
       MAX(data_ordine) AS ultimo_ordine
FROM   ordini;

-- Con GROUP BY — min/max per categoria
SELECT c.nome AS categoria,
       MIN(p.prezzo) AS prezzo_min,
       MAX(p.prezzo) AS prezzo_max,
       MAX(p.prezzo) - MIN(p.prezzo) AS range_prezzi
FROM   prodotti p
JOIN   categorie c ON p.categoria_id = c.id
GROUP BY c.nome
ORDER BY range_prezzi DESC;

GREATEST e LEAST (su colonne multiple)

-- GREATEST e LEAST confrontano valori su più colonne (non aggregate)
SELECT
  nome,
  GREATEST(prezzo, costo_produzione, 10) AS valore_max_campi,
  LEAST(stock, stock_minimo, 100)        AS valore_min_campi
FROM prodotti;

-- MIN/MAX su stringhe (ordine lessicografico)
SELECT MIN(nome) AS primo_alfa, MAX(nome) AS ultimo_alfa
FROM clienti;

PostgreSQL — COUNT

COUNT() conta il numero di righe o valori non-NULL. È la funzione aggregata più comune e si combina con GROUP BY e HAVING per analisi raggruppate.

COUNT

-- Conta tutte le righe
SELECT COUNT(*) AS totale_prodotti FROM prodotti;

-- Conta solo i valori non-NULL in una colonna
SELECT COUNT(email) AS clienti_con_email FROM clienti;

-- Conta valori distinti
SELECT COUNT(DISTINCT categoria_id) AS num_categorie FROM prodotti;

-- Conta con filtro (usando FILTER)
SELECT
  COUNT(*)                               AS totale,
  COUNT(*) FILTER (WHERE prezzo < 50)    AS economici,
  COUNT(*) FILTER (WHERE prezzo >= 50 AND prezzo < 200) AS medi,
  COUNT(*) FILTER (WHERE prezzo >= 200)  AS premium
FROM prodotti;

COUNT con GROUP BY

-- Numero di prodotti per categoria
SELECT c.nome AS categoria, COUNT(p.id) AS num_prodotti
FROM   categorie c
LEFT JOIN prodotti p ON p.categoria_id = c.id
GROUP BY c.id, c.nome
ORDER BY num_prodotti DESC;

-- Clienti con almeno 2 ordini
SELECT cliente_id, COUNT(*) AS num_ordini
FROM   ordini
GROUP BY cliente_id
HAVING COUNT(*) >= 2
ORDER BY num_ordini DESC;

PostgreSQL — SUM

SUM() calcola la somma dei valori di una colonna numerica. Ignora i valori NULL. Restituisce NULL se non ci sono righe (usa COALESCE(SUM(...), 0) per restituire 0).

SUM

-- Totale stock
SELECT SUM(stock) AS stock_totale FROM prodotti;

-- Con COALESCE per evitare NULL
SELECT COALESCE(SUM(totale), 0) AS fatturato_totale
FROM   ordini WHERE stato = ''consegnato'';

-- Fatturato per cliente
SELECT c.nome, SUM(o.totale) AS fatturato
FROM   clienti c
JOIN   ordini o ON o.cliente_id = c.id
WHERE  o.stato != ''annullato''
GROUP BY c.id, c.nome
ORDER BY fatturato DESC;

-- SUM con FILTER
SELECT
  SUM(totale)                                  AS totale_generale,
  SUM(totale) FILTER (WHERE stato = ''consegnato'') AS consegnati,
  SUM(totale) FILTER (WHERE stato = ''annullato'')  AS annullati
FROM ordini;

Running total con window function

-- Totale cumulativo (somma progressiva)
SELECT
  data_ordine,
  totale,
  SUM(totale) OVER (ORDER BY data_ordine) AS totale_cumulativo
FROM ordini
ORDER BY data_ordine;

PostgreSQL — AVG

AVG() calcola la media aritmetica dei valori non-NULL di una colonna. Restituisce DOUBLE PRECISION per valori interi e NUMERIC per tipo NUMERIC.

AVG

-- Media prezzi
SELECT AVG(prezzo) AS prezzo_medio FROM prodotti;

-- Arrotondato
SELECT ROUND(AVG(prezzo), 2) AS prezzo_medio FROM prodotti;

-- Media per categoria
SELECT c.nome, ROUND(AVG(p.prezzo), 2) AS prezzo_medio
FROM   prodotti p
JOIN   categorie c ON p.categoria_id = c.id
GROUP BY c.id, c.nome
ORDER BY prezzo_medio DESC;

-- Confronta ogni prodotto con la media della sua categoria
SELECT
  p.nome,
  p.prezzo,
  ROUND(AVG(p2.prezzo), 2) AS media_categoria,
  p.prezzo - AVG(p2.prezzo) AS diff
FROM prodotti p
JOIN prodotti p2 ON p.categoria_id = p2.categoria_id
GROUP BY p.id, p.nome, p.prezzo
ORDER BY diff DESC;

AVG con window function

-- Media mobile (3 ordini precedenti)
SELECT
  data_ordine,
  totale,
  ROUND(AVG(totale) OVER (
    ORDER BY data_ordine
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ), 2) AS media_mobile_3
FROM ordini
ORDER BY data_ordine;

PostgreSQL — LIKE

LIKE filtra le stringhe con pattern. Il simbolo % corrisponde a qualsiasi sequenza di caratteri, _ a un singolo carattere. ILIKE è la versione case-insensitive (estensione PostgreSQL).

LIKE e ILIKE

-- Inizia con "Lap"
SELECT * FROM prodotti WHERE nome LIKE ''Lap%'';

-- Finisce con "Pro"
SELECT * FROM prodotti WHERE nome LIKE ''%Pro'';

-- Contiene "wire" (case-insensitive)
SELECT * FROM prodotti WHERE nome ILIKE ''%wire%'';

-- Un carattere specifico nella terza posizione
SELECT * FROM clienti WHERE nome LIKE ''__a%'';   -- 3° lettera è "a"

-- NOT LIKE
SELECT * FROM prodotti WHERE nome NOT LIKE ''%obsoleto%'';

-- Escape del carattere speciale
SELECT * FROM prezzi WHERE codice LIKE ''100\%'' ESCAPE '''';

SIMILAR TO e regex POSIX

-- SIMILAR TO (regex SQL standard, più limitato)
SELECT * FROM prodotti WHERE ean SIMILAR TO ''[0-9]{13}'';

-- ~ regex POSIX (case-sensitive)
SELECT * FROM email WHERE email ~ ''^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$'';

-- ~* regex POSIX (case-insensitive)
SELECT * FROM prodotti WHERE nome ~* ''(pro|plus|max)$'';

-- Full-text search (per ricerche più complete)
SELECT * FROM articoli
WHERE to_tsvector(''italian'', contenuto) @@ to_tsquery(''italian'', ''database'');

PostgreSQL — IN

IN verifica se un valore appartiene a un insieme di valori o ai risultati di una subquery. NOT IN esclude i valori dell'insieme.

IN con lista e subquery

-- IN con lista di valori
SELECT * FROM clienti WHERE citta IN (''Roma'', ''Milano'', ''Torino'');
SELECT * FROM ordini  WHERE stato IN (''spedito'', ''consegnato'');

-- NOT IN
SELECT * FROM prodotti WHERE categoria_id NOT IN (1, 2);

-- IN con subquery
SELECT nome FROM clienti
WHERE  id IN (
  SELECT DISTINCT cliente_id
  FROM   ordini
  WHERE  totale > 500
);

-- IN con array (specifico di PostgreSQL)
SELECT * FROM prodotti WHERE id = ANY(ARRAY[1, 5, 10]);

IN vs EXISTS — quando usare quale

-- IN: il set di valori è piccolo, la subquery è veloce
SELECT * FROM prodotti
WHERE categoria_id IN (SELECT id FROM categorie WHERE attiva = TRUE);

-- EXISTS: preferibile quando la subquery potrebbe restituire NULL
--         o quando la correlazione è sulla tabella esterna
SELECT * FROM clienti c
WHERE EXISTS (
  SELECT 1 FROM ordini o
  WHERE o.cliente_id = c.id AND o.totale > 1000
);

-- Attenzione: NOT IN con NULL nella subquery restituisce SEMPRE vuoto!
-- Usa NOT EXISTS in questi casi:
SELECT * FROM clienti c
WHERE NOT EXISTS (
  SELECT 1 FROM ordini o WHERE o.cliente_id = c.id
);

PostgreSQL — BETWEEN

BETWEEN verifica se un valore è compreso tra due limiti (inclusivi). Funziona con numeri, date, stringhe e qualsiasi tipo ordinabile.

BETWEEN su numeri, date e stringhe

-- Numeri (inclusi i limiti)
SELECT * FROM prodotti WHERE prezzo BETWEEN 50 AND 200;

-- Date
SELECT * FROM ordini
WHERE  data_ordine BETWEEN ''2024-01-01'' AND ''2024-12-31'';

-- TIMESTAMPTZ (attenzione alle ore!)
SELECT * FROM log
WHERE  creato BETWEEN ''2024-06-01 00:00:00'' AND ''2024-06-30 23:59:59'';
-- Meglio usare:
WHERE creato >= ''2024-06-01'' AND creato < ''2024-07-01''

-- NOT BETWEEN
SELECT * FROM prodotti WHERE prezzo NOT BETWEEN 100 AND 500;

-- Stringhe (ordine lessicografico)
SELECT * FROM prodotti WHERE nome BETWEEN ''A'' AND ''M'';

📌 BETWEEN a AND b è equivalente a >= a AND <= b — entrambi i limiti sono inclusivi. Per escludere un estremo usa gli operatori > / < direttamente. Con le date, preferisci >= inizio AND < fine per evitare problemi con le ore.

PostgreSQL — AS (Alias)

AS assegna un alias (nome temporaneo) a colonne, espressioni o tabelle all'interno di una query. Rende i risultati più leggibili e permette di fare riferimento a espressioni complesse in modo più semplice.

Alias su colonne

-- Alias per colonne
SELECT
  nome         AS "Nome Prodotto",
  prezzo       AS prezzo_euro,
  prezzo * 1.22 AS prezzo_iva,
  stock > 0    AS disponibile
FROM prodotti;

-- AS è facoltativo (ma raccomandato per chiarezza)
SELECT nome "Nome", prezzo "Prezzo €" FROM prodotti;

-- Alias con spazi richiedono le virgolette doppie
SELECT nome AS "Nome del Prodotto" FROM prodotti;

Alias su tabelle (table alias)

-- Alias per le tabelle (utile nei JOIN)
SELECT p.nome, c.nome AS categoria, f.nome AS fornitore
FROM   prodotti p
JOIN   categorie c ON p.categoria_id = c.id
JOIN   fornitori f ON p.fornitore_id = f.id;

-- Self-join con alias (trova manager di ogni dipendente)
SELECT d.nome AS dipendente, m.nome AS manager
FROM   dipendenti d
JOIN   dipendenti m ON d.manager_id = m.id;

CTE (Common Table Expression) — alias di subquery

-- WITH definisce una query "con nome" (CTE)
WITH ordini_grandi AS (
  SELECT cliente_id, SUM(totale) AS totale_speso
  FROM   ordini
  WHERE  stato = ''consegnato''
  GROUP BY cliente_id
  HAVING SUM(totale) > 1000
)
SELECT c.nome, og.totale_speso
FROM   clienti c
JOIN   ordini_grandi og ON c.id = og.cliente_id
ORDER BY og.totale_speso DESC;

PostgreSQL — Joins

Un JOIN combina righe di due o più tabelle basandosi su una condizione di collegamento (di solito una chiave esterna). PostgreSQL supporta tutti i tipi di JOIN dello standard SQL.

Tipi di JOIN

Tipo	Righe restituite
`INNER JOIN`	Solo le righe con corrispondenza in entrambe le tabelle
`LEFT JOIN`	Tutte le righe della tabella sinistra + corrispondenze (NULL se assente)
`RIGHT JOIN`	Tutte le righe della tabella destra + corrispondenze (NULL se assente)
`FULL JOIN`	Tutte le righe di entrambe le tabelle (NULL dove mancano corrispondenze)
`CROSS JOIN`	Prodotto cartesiano (ogni riga con ogni riga)

JOIN — panoramica

-- Schema: prodotti → categorie (FK: categoria_id)
--         ordini    → clienti  (FK: cliente_id)
--         ordini    → ordine_dettagli (FK: ordine_id)

-- INNER JOIN base
SELECT p.nome, c.nome AS categoria
FROM   prodotti p
INNER JOIN categorie c ON p.categoria_id = c.id;

-- JOIN multipli
SELECT o.id, cl.nome AS cliente, p.nome AS prodotto,
       od.quantita, od.prezzo_unit
FROM   ordini o
JOIN   clienti cl        ON o.cliente_id  = cl.id
JOIN   ordine_dettagli od ON od.ordine_id  = o.id
JOIN   prodotti p        ON od.prodotto_id = p.id
ORDER BY o.id;

PostgreSQL — INNER JOIN

INNER JOIN (o semplicemente JOIN) restituisce solo le righe dove esiste una corrispondenza in entrambe le tabelle. Le righe senza corrispondenza vengono escluse.

INNER JOIN

-- Prodotti con la loro categoria (esclude prodotti senza categoria)
SELECT p.nome, p.prezzo, c.nome AS categoria
FROM   prodotti p
INNER JOIN categorie c ON p.categoria_id = c.id
ORDER BY c.nome, p.prezzo;

-- Ordini con dettagli e prodotti
SELECT o.id AS ordine, o.data_ordine,
       p.nome AS prodotto, od.quantita,
       od.quantita * od.prezzo_unit AS subtotale
FROM   ordini o
JOIN   ordine_dettagli od ON od.ordine_id  = o.id
JOIN   prodotti p         ON p.id = od.prodotto_id
WHERE  o.stato = ''consegnato''
ORDER BY o.id;

JOIN con aggregazioni

-- Fatturato per categoria (solo categorie con ordini)
SELECT c.nome AS categoria,
       COUNT(DISTINCT o.id)   AS num_ordini,
       SUM(od.quantita)       AS pezzi_venduti,
       SUM(od.quantita * od.prezzo_unit) AS fatturato
FROM   categorie c
JOIN   prodotti p  ON p.categoria_id = c.id
JOIN   ordine_dettagli od ON od.prodotto_id = p.id
JOIN   ordini o    ON o.id = od.ordine_id
GROUP BY c.id, c.nome
ORDER BY fatturato DESC;

PostgreSQL — LEFT JOIN

LEFT JOIN restituisce tutte le righe della tabella sinistra, più le colonne della tabella destra dove esiste una corrispondenza. Se non c'è corrispondenza, le colonne destre sono NULL.

LEFT JOIN

-- Tutte le categorie, con il numero di prodotti (anche 0)
SELECT c.nome AS categoria, COUNT(p.id) AS num_prodotti
FROM   categorie c
LEFT JOIN prodotti p ON p.categoria_id = c.id
GROUP BY c.id, c.nome
ORDER BY num_prodotti DESC;

-- Clienti con o senza ordini
SELECT c.nome, COUNT(o.id) AS num_ordini,
       COALESCE(SUM(o.totale), 0) AS totale_speso
FROM   clienti c
LEFT JOIN ordini o ON o.cliente_id = c.id
GROUP BY c.id, c.nome
ORDER BY totale_speso DESC;

Anti-join con LEFT JOIN (trova le eccezioni)

-- Clienti che NON hanno mai ordinato
SELECT c.nome, c.email
FROM   clienti c
LEFT JOIN ordini o ON o.cliente_id = c.id
WHERE  o.id IS NULL;   -- filtra le righe senza corrispondenza

-- Prodotti mai ordinati
SELECT p.nome, p.prezzo
FROM   prodotti p
LEFT JOIN ordine_dettagli od ON od.prodotto_id = p.id
WHERE  od.ordine_id IS NULL;

PostgreSQL — RIGHT JOIN

RIGHT JOIN è lo speculare di LEFT JOIN: restituisce tutte le righe della tabella destra, più le colonne della tabella sinistra dove esiste una corrispondenza. Nella pratica si usa raramente — di solito si preferisce un LEFT JOIN con l'ordine delle tabelle invertito.

RIGHT JOIN

-- Equivalente: tutti i fornitori, anche senza prodotti
-- (RIGHT JOIN con prodotti a sinistra e fornitori a destra)
SELECT f.nome AS fornitore, COUNT(p.id) AS num_prodotti
FROM   prodotti p
RIGHT JOIN fornitori f ON p.fornitore_id = f.id
GROUP BY f.id, f.nome
ORDER BY num_prodotti DESC;

-- Lo stesso risultato con LEFT JOIN (più leggibile):
SELECT f.nome AS fornitore, COUNT(p.id) AS num_prodotti
FROM   fornitori f
LEFT JOIN prodotti p ON p.fornitore_id = f.id
GROUP BY f.id, f.nome
ORDER BY num_prodotti DESC;

💡 In pratica, evita RIGHT JOIN: è sempre convertibile in un LEFT JOIN invertendo l'ordine delle tabelle. Usare solo LEFT JOIN rende il codice SQL più facile da leggere e mantenere.

PostgreSQL — FULL JOIN

FULL JOIN (o FULL OUTER JOIN) combina i risultati di LEFT JOIN e RIGHT JOIN: restituisce tutte le righe di entrambe le tabelle, con NULL dove non c'è corrispondenza.

FULL JOIN

-- Tutte le categorie E tutti i prodotti
-- (mostra anche categorie senza prodotti e prodotti senza categoria)
SELECT c.nome AS categoria, p.nome AS prodotto
FROM   categorie c
FULL JOIN prodotti p ON p.categoria_id = c.id
ORDER BY c.nome NULLS LAST, p.nome;

-- Confronto stock vs ordini
SELECT p.nome,
       p.stock         AS stock_attuale,
       SUM(od.quantita) AS totale_ordinato
FROM   prodotti p
FULL JOIN ordine_dettagli od ON od.prodotto_id = p.id
GROUP BY p.id, p.nome, p.stock
ORDER BY p.nome;

FULL JOIN per trovare differenze tra tabelle

-- Trova differenze tra due tabelle (utile per sincronizzazione)
SELECT
  COALESCE(a.id, b.id)     AS id,
  a.nome AS nome_in_A,
  b.nome AS nome_in_B,
  CASE
    WHEN a.id IS NULL THEN ''Solo in B''
    WHEN b.id IS NULL THEN ''Solo in A''
    ELSE ''In entrambi''
  END AS situazione
FROM tabella_a a
FULL JOIN tabella_b b ON a.id = b.id
WHERE a.id IS NULL OR b.id IS NULL;   -- solo le differenze

PostgreSQL — CROSS JOIN

CROSS JOIN produce il prodotto cartesiano di due tabelle: ogni riga della prima combinata con ogni riga della seconda. N righe × M righe = N×M righe di output. Raramente usato direttamente, ma utile per generare combinazioni.

CROSS JOIN

-- Ogni colore con ogni taglia
SELECT colori.nome AS colore, taglie.nome AS taglia
FROM (VALUES (''Rosso''),(''Blu''),(''Verde'')) AS colori(nome)
CROSS JOIN (VALUES (''S''),(''M''),(''L''),(''XL'')) AS taglie(nome)
ORDER BY colore, taglia;
-- 3 × 4 = 12 combinazioni

-- Genera una serie di date × categorie
SELECT d::DATE AS giorno, c.nome AS categoria
FROM generate_series(''2024-01-01''::DATE, ''2024-01-07''::DATE, ''1 day'') d
CROSS JOIN categorie c
ORDER BY d, c.nome;

📌 CROSS JOIN senza condizione WHERE è raramente quello che vuoi in produzione — attenzione alle tabelle grandi! 1000 × 1000 = 1.000.000 di righe. Spesso viene usato con una WHERE aggiuntiva, ma in quel caso equivale a un INNER JOIN.

PostgreSQL — UNION

UNION combina i risultati di due o più query in un unico set. Le query devono avere lo stesso numero di colonne e tipi compatibili. UNION rimuove i duplicati; UNION ALL li mantiene (più veloce).

UNION e UNION ALL

-- Unisce clienti da due fonti diverse
SELECT nome, email, ''online''  AS canale FROM clienti_web
UNION
SELECT nome, email, ''negozio'' AS canale FROM clienti_fisici
ORDER BY nome;

-- UNION ALL mantiene i duplicati (più efficiente)
SELECT nome, email FROM clienti_attivi
UNION ALL
SELECT nome, email FROM ex_clienti;

-- UNION con più query
SELECT ''Entrate'' AS tipo, SUM(totale) AS importo
FROM ordini WHERE stato = ''consegnato''
UNION ALL
SELECT ''Rimborsi'', -SUM(importo)
FROM rimborsi
UNION ALL
SELECT ''Costi'', -SUM(costo)
FROM spese;

INTERSECT e EXCEPT

-- INTERSECT — solo le righe presenti in entrambe
SELECT email FROM newsletter_iscritti
INTERSECT
SELECT email FROM clienti;  -- clienti iscritti alla newsletter

-- EXCEPT — righe nella prima ma non nella seconda
SELECT email FROM newsletter_iscritti
EXCEPT
SELECT email FROM clienti;  -- iscritti che non sono ancora clienti

PostgreSQL — GROUP BY

GROUP BY raggruppa le righe con valori uguali nelle colonne specificate, permettendo di applicare funzioni aggregate (COUNT, SUM, AVG, MIN, MAX) a ogni gruppo.

GROUP BY base

-- Numero di prodotti per categoria
SELECT categoria_id, COUNT(*) AS num_prodotti
FROM   prodotti
GROUP BY categoria_id
ORDER BY num_prodotti DESC;

-- Aggregazioni multiple
SELECT c.nome AS categoria,
       COUNT(p.id)           AS num_prodotti,
       ROUND(AVG(p.prezzo), 2) AS prezzo_medio,
       SUM(p.stock)          AS stock_totale,
       MIN(p.prezzo)         AS prezzo_min,
       MAX(p.prezzo)         AS prezzo_max
FROM   categorie c
LEFT JOIN prodotti p ON p.categoria_id = c.id
GROUP BY c.id, c.nome
ORDER BY num_prodotti DESC;

GROUPING SETS, ROLLUP, CUBE

-- ROLLUP — subtotali gerarchici
SELECT categoria_id, fornitore_id, SUM(stock) AS stock_tot
FROM   prodotti
GROUP BY ROLLUP(categoria_id, fornitore_id);
-- Genera: (cat, forn), (cat), () — totale complessivo

-- CUBE — tutte le combinazioni di raggruppamento
SELECT paese, citta, COUNT(*) AS num_clienti
FROM   clienti
GROUP BY CUBE(paese, citta);

-- GROUPING SETS — specificato manualmente
SELECT categoria_id, fornitore_id, SUM(prezzo) AS totale
FROM   prodotti
GROUP BY GROUPING SETS (
  (categoria_id, fornitore_id),
  (categoria_id),
  ()
);

PostgreSQL — HAVING

HAVING filtra i gruppi dopo l'aggregazione (come WHERE filtra le righe prima). Si usa sempre insieme a GROUP BY e può contenere funzioni aggregate.

HAVING

-- Categorie con più di 3 prodotti
SELECT categoria_id, COUNT(*) AS num_prodotti
FROM   prodotti
GROUP BY categoria_id
HAVING COUNT(*) > 3
ORDER BY num_prodotti DESC;

-- Clienti con fatturato totale > 500€
SELECT c.nome, SUM(o.totale) AS fatturato
FROM   clienti c
JOIN   ordini o ON o.cliente_id = c.id
GROUP BY c.id, c.nome
HAVING SUM(o.totale) > 500
ORDER BY fatturato DESC;

-- HAVING con più condizioni
SELECT categoria_id,
       ROUND(AVG(prezzo), 2) AS prezzo_medio,
       SUM(stock) AS stock_totale
FROM   prodotti
GROUP BY categoria_id
HAVING AVG(prezzo) > 20
   AND SUM(stock) > 50
ORDER BY prezzo_medio DESC;

Clausola	Quando filtra	Può usare aggregate?
`WHERE`	Prima del GROUP BY (su righe singole)	No
`HAVING`	Dopo il GROUP BY (su gruppi)	Sì

WHERE + HAVING insieme

-- WHERE filtra prima del raggruppamento,
-- HAVING filtra dopo
SELECT c.nome AS categoria,
       COUNT(p.id)             AS num_prodotti,
       SUM(p.stock)            AS stock_totale
FROM   categorie c
JOIN   prodotti p ON p.categoria_id = c.id
WHERE  p.discontinuato = FALSE          -- esclude prodotti discontinuati
GROUP BY c.id, c.nome
HAVING SUM(p.stock) > 0                 -- esclude categorie senza stock
ORDER BY stock_totale DESC;

PostgreSQL — EXISTS

EXISTS verifica se una subquery restituisce almeno una riga. È spesso più efficiente di IN su subquery correlate perché si ferma alla prima corrispondenza trovata.

EXISTS e NOT EXISTS

-- Clienti che hanno fatto almeno un ordine
SELECT c.nome, c.email
FROM   clienti c
WHERE  EXISTS (
  SELECT 1 FROM ordini o WHERE o.cliente_id = c.id
);

-- Clienti senza ordini (NOT EXISTS)
SELECT c.nome, c.email
FROM   clienti c
WHERE  NOT EXISTS (
  SELECT 1 FROM ordini o WHERE o.cliente_id = c.id
);

-- Prodotti con almeno un ordine > 5 pezzi
SELECT p.nome, p.prezzo
FROM   prodotti p
WHERE  EXISTS (
  SELECT 1
  FROM   ordine_dettagli od
  WHERE  od.prodotto_id = p.id
    AND  od.quantita > 5
);

EXISTS vs IN — differenza importante

-- IN: pericoloso con NULL nella subquery!
-- Se la subquery restituisce anche un NULL, NOT IN restituisce ZERO righe
SELECT * FROM a WHERE id NOT IN (SELECT id FROM b);  -- ATTENZIONE se b.id ha NULL!

-- NOT EXISTS: gestisce correttamente i NULL
SELECT * FROM a WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.id = a.id);
-- Sicuro anche se b.id contiene NULL

-- Regola: usa EXISTS/NOT EXISTS per subquery correlate
-- usa IN per liste di valori statici o subquery piccole e senza NULL

PostgreSQL — ANY

ANY (alias SOME) confronta un valore con i risultati di una subquery o un array: restituisce TRUE se il confronto è vero per almeno un valore del set.

ANY con operatori

-- Prodotti con prezzo maggiore di ALMENO uno dei prodotti economici
SELECT nome, prezzo
FROM   prodotti
WHERE  prezzo > ANY (
  SELECT prezzo FROM prodotti WHERE categoria_id = 3
);

-- = ANY equivale a IN
SELECT * FROM clienti
WHERE  citta = ANY (ARRAY[''Roma'', ''Milano'', ''Torino'']);

-- != ANY equivale a "diverso da almeno uno" (NON equivale a NOT IN!)
SELECT * FROM prodotti
WHERE prezzo != ANY (SELECT prezzo FROM prezzi_bloccati);
-- Attenzione: restituisce quasi sempre tutte le righe!

-- Operatori supportati: =, !=, <, >, <=, >=
SELECT * FROM prodotti
WHERE stock < ANY (SELECT stock_minimo FROM soglie_alert);

💡 = ANY(array) è equivalente a IN e spesso più flessibile perché funziona con array PostgreSQL oltre che con subquery. != ANY NON è equivalente a NOT IN — per "diverso da tutti" usa != ALL.

PostgreSQL — ALL

ALL confronta un valore con tutti i risultati di una subquery: restituisce TRUE solo se il confronto è vero per tutti i valori del set.

ALL con operatori

-- Prodotto più caro di TUTTI i prodotti della categoria 2
SELECT nome, prezzo
FROM   prodotti
WHERE  prezzo > ALL (
  SELECT prezzo FROM prodotti WHERE categoria_id = 2
);

-- Clienti il cui totale ordini è maggiore di TUTTI gli ordini di Mario
SELECT c.nome, SUM(o.totale) AS totale
FROM   clienti c
JOIN   ordini o ON o.cliente_id = c.id
GROUP BY c.id, c.nome
HAVING SUM(o.totale) > ALL (
  SELECT totale FROM ordini
  WHERE cliente_id = (SELECT id FROM clienti WHERE nome = ''Mario Rossi'')
);

-- != ALL equivale a NOT IN (ma sicuro con NULL)
SELECT * FROM prodotti
WHERE categoria_id != ALL (ARRAY[1, 2, 5]);

Operatore	ANY	ALL
`= X`	Equivale a IN (X)	Equivale a X solo se tutti uguali
`!= X`	Vero se almeno uno è diverso (quasi sempre vero)	Equivale a NOT IN (X)
`> X`	Vero se X > almeno uno (min)	Vero se X > tutti (max)
`< X`	Vero se X < almeno uno (max)	Vero se X < tutti (min)

PostgreSQL — CASE

CASE è l'espressione condizionale di SQL: equivale a if/else e può essere usata in SELECT, WHERE, ORDER BY, GROUP BY e nei parametri delle funzioni aggregate.

CASE semplice e ricercato

-- CASE semplice (confronto con un valore)
SELECT nome,
  CASE categoria_id
    WHEN 1 THEN ''Elettronica''
    WHEN 2 THEN ''Abbigliamento''
    WHEN 3 THEN ''Alimentari''
    ELSE ''Altro''
  END AS categoria_nome
FROM prodotti;

-- CASE ricercato (condizioni booleane)
SELECT nome, prezzo,
  CASE
    WHEN prezzo < 20   THEN ''Economico''
    WHEN prezzo < 100  THEN ''Medio''
    WHEN prezzo < 500  THEN ''Alto''
    ELSE ''Premium''
  END AS fascia_prezzo
FROM prodotti
ORDER BY prezzo;

CASE nelle aggregazioni

-- Pivot manuale con CASE + SUM
SELECT
  EXTRACT(MONTH FROM data_ordine) AS mese,
  SUM(CASE WHEN stato = ''consegnato'' THEN totale ELSE 0 END) AS consegnati,
  SUM(CASE WHEN stato = ''annullato''  THEN totale ELSE 0 END) AS annullati,
  COUNT(CASE WHEN stato = ''in attesa'' THEN 1 END)            AS in_attesa
FROM ordini
GROUP BY mese
ORDER BY mese;

-- CASE nell''ORDER BY (ordinamento personalizzato)
SELECT nome, stato
FROM   ordini
ORDER BY
  CASE stato
    WHEN ''in attesa''  THEN 1
    WHEN ''confermato'' THEN 2
    WHEN ''spedito''    THEN 3
    WHEN ''consegnato'' THEN 4
    ELSE 5
  END;

COALESCE e NULLIF (scorciatoie)

-- COALESCE restituisce il primo valore non-NULL
SELECT nome, COALESCE(telefono, email, ''N/D'') AS contatto
FROM clienti;

-- NULLIF restituisce NULL se i due valori sono uguali
SELECT nome, NULLIF(sconto, 0) AS sconto  -- NULL se sconto=0
FROM prodotti;

-- Combina NULLIF e COALESCE
SELECT nome,
  COALESCE(NULLIF(TRIM(note), \'\'), \'Nessuna nota\') AS note_pulite
FROM ordini;

Esercizi PostgreSQL

Metti alla prova le tue conoscenze con questi esercizi pratici sul database demo Northwind creato nella sezione precedente. Usa neon.tech o un'installazione locale.

Esercizio 1 — Query base

🟢 Base

Usando il database demo Northwind, scrivi le query per:

Elenca tutti i prodotti ordinati per prezzo decrescente
Mostra nome e prezzo dei prodotti con prezzo tra 20€ e 100€
Conta quanti clienti ci sono per ogni città
Trova il prodotto più caro e quello più economico per categoria
Elenca i clienti che hanno effettuato almeno un ordine nel 2024

Esercizio 2 — JOIN e aggregazioni

🟡 Intermedio

Mostra nome cliente, data ordine e totale per tutti gli ordini con stato "consegnato"
Calcola il fatturato totale per ogni fornitore (somma dei prezzi dei prodotti venduti × quantità)
Trova i 3 prodotti più venduti (per quantità totale)
Elenca i clienti che non hanno mai effettuato ordini (usa LEFT JOIN o NOT EXISTS)
Mostra il riepilogo mensile degli ordini del 2024: mese, numero ordini, totale fatturato

Esercizio 3 — Avanzato

🔴 Avanzato

Scrivi una CTE che calcola il "valore totale del magazzino" (prezzo × stock) per categoria, poi mostra solo le categorie sopra la media
Usa una window function per assegnare un ranking ai prodotti per prezzo all'interno di ogni categoria
Trova i clienti "VIP": quelli nel top 20% per fatturato totale (usa NTILE o PERCENT_RANK)
Scrivi una query UPSERT che aggiorna i prezzi dei prodotti da una tabella temporanea nuovi_prezzi(ean, prezzo)
Crea una vista materializzata mv_fatturato_mensile con il fatturato per mese e categoria

Piano di Studio PostgreSQL

Segui questo percorso per padroneggiare PostgreSQL da zero, con focus pratico e tempi realistici.

Settimana	Argomenti	Obiettivo pratico
1	Installazione, psql, pgAdmin, CREATE TABLE, INSERT, SELECT base	Creare e popolare il database demo Northwind
2	WHERE, ORDER BY, LIMIT, JOIN (INNER, LEFT)	Query su più tabelle con filtri e ordinamento
3	GROUP BY, HAVING, funzioni aggregate (COUNT, SUM, AVG, MIN, MAX)	Report e statistiche di vendita
4	Subquery, EXISTS, IN, ALL, ANY, CASE, alias, CTE	Query complesse e condizionali
5	Indici, EXPLAIN ANALYZE, ottimizzazione, VACUUM	Capire e migliorare le performance
6	Transazioni, vincoli, trigger, funzioni PL/pgSQL	Garantire l'integrità dei dati
7	Window functions, JSONB, array, full-text search	Funzionalità avanzate di PostgreSQL
8	Replica, backup, pg_dump, deploy su cloud, integrazione Node.js/Python	Mettere in produzione un'applicazione reale

Risorse consigliate

PostgreSQL Documentation — la documentazione ufficiale, eccellente e completa
neon.tech — PostgreSQL serverless gratuito per esercitarsi
pgexercises.com — esercizi interattivi progressivi con soluzione
explain.dalibo.com — visualizzatore grafico di EXPLAIN JSON
use-the-index-luke.com — guida approfondita sugli indici SQL

Conclusione — E ora?

Complimenti! 🎉 Hai completato il corso PostgreSQL completo e gratuito in italiano: dall'installazione e i primi passi con psql e pgAdmin, all'intero schema di un database relazionale, alle query SQL avanzate, JOIN, aggregazioni, window functions e molto altro.

PostgreSQL è oggi il database relazionale open-source più apprezzato dal mondo enterprise: banche, startup, servizi cloud e applicazioni ad alta disponibilità lo usano ovunque. Con queste basi sei pronto a integrarlo in applicazioni reali.

E ora?

Ora che padroneggi i database relazionali con PostgreSQL, il passo successivo naturale è imparare Django (che usa PostgreSQL nativamente come database preferito) per costruire applicazioni web complete con il backend Python, oppure approfondire Node.js per integrare PostgreSQL in API REST moderne. Se vuoi un percorso guidato personalizzato, posso seguirti con ripetizioni di informatica in presenza a Roma.

🎯 La tua sfida finale

Progetta e implementa da zero un database e-learning in PostgreSQL: tabelle per studenti, corsi, lezioni, iscrizioni e progressi. Aggiungi vincoli di integrità, indici sulle colonne più usate e almeno 3 viste utili per i report. Poi connettilo a una semplice API REST con Node.js o Django. Sarà il progetto portfolio che dimostra la tua padronanza dei database relazionali.

🐍 Continua con Django 🚀 Torna ai Corsi 📅 Prenota una lezione