Tutorial PostgreSQL con esempi eseguibili
Un tutorial PostgreSQL gratuito con query che girano nella pagina: tipi di dati, CREATE TABLE, upsert, JSONB, date, CTE, funzioni finestra, indici e VACUUM. Modifica una query ed eseguila di nuovo.
Inizia il corsoPrimi passi
- PostgreSQL vs MySQLPostgreSQL e MySQL sono entrambi database relazionali gratuiti. PostgreSQL ha l'SQL più ricco, JSONB e DDL transazionale; MySQL è più semplice da gestire dove è già la scelta predefinita.
- PostgreSQL vs SQL ServerPostgreSQL è gratuito e open source; SQL Server è il database commerciale di Microsoft, con T-SQL e strumenti pensati per Windows. Ecco come cambiano SQL, JSON, lock e licenze.
- PostgreSQL vs MongoDBPostgreSQL salva righe in tabelle con uno schema fisso e può contenere documenti JSON in colonne jsonb; MongoDB salva documenti con schema flessibile in collezioni e li distribuisce su più server con lo sharding.
- Elenco database e tabelleIn psql, \l elenca i database, \c passa a uno di essi e \dt ne elenca le tabelle. In SQL, interroga pg_database, information_schema.tables e information_schema.columns.
Tabelle e tipi
- Tipi di datiI tipi di dati di PostgreSQL che userai per quasi ogni colonna: interi, numeric, text, boolean, timestamptz, uuid, jsonb, array ed enum, con quando scegliere ciascuno.
- CREATE TABLECREATE TABLE definisce colonne, tipi e vincoli di una tabella. Copre chiavi identity, NOT NULL, DEFAULT, UNIQUE, CHECK, IF NOT EXISTS, CREATE TABLE AS e LIKE.
- ALTER TABLEALTER TABLE modifica una tabella esistente: aggiungi, rinomina ed elimina colonne, cambia il tipo di una colonna con USING, imposta valori predefiniti e NOT NULL e aggiungi vincoli.
- Chiavi primarieCome definire una chiave primaria in PostgreSQL e come generarla: colonne identity, il vecchio SERIAL e gli UUID con gen_random_uuid(). Più chiavi composte e il reset di una sequenza.
- Chiavi esterneUna chiave esterna fa accettare a una colonna solo valori che esistono in un'altra tabella. Copre REFERENCES, ON DELETE CASCADE, SET NULL e RESTRICT, come aggiungere una chiave a una tabella esistente e come indicizzarla.
- Tipi enumUn tipo enum è una lista fissa e ordinata di valori ammessi, creata con CREATE TYPE ... AS ENUM. Copre come aggiungere e rinominare valori, elencarli, ordinarli e quando conviene invece un vincolo CHECK.
- Timestamptimestamptz memorizza un istante e lo mostra nel fuso orario della sessione; timestamp memorizza un orario da orologio a muro senza fuso. Nessuno dei due conserva il fuso che hai scritto. Usa timestamptz quasi sempre.
Query
- JoinUn join combina le righe di due tabelle in base a una condizione. INNER JOIN tiene le corrispondenze, LEFT JOIN tiene ogni riga della prima tabella, FULL JOIN tiene quelle di entrambe, CROSS JOIN abbina tutto e LATERAL esegue una subquery per riga.
- DISTINCT ONSELECT DISTINCT ON (key) ... ORDER BY key, sort tiene la prima riga di ogni gruppo, come l'ultimo ordine di ogni cliente. Esiste solo in PostgreSQL.
- COALESCE, NULLIF e CASECOALESCE(a, b, ...) restituisce il primo argomento che non è NULL, NULLIF(a, b) trasforma un valore in NULL e CASE WHEN sceglie un valore in base a una condizione.
- LIKE, ILIKE e regexLIKE confronta il testo con un pattern che usa % e _, ILIKE fa lo stesso ignorando maiuscole e minuscole, e gli operatori ~ confrontano espressioni regolari POSIX.
- CAST e ::CAST(value AS type) e value::type convertono un valore in un altro tipo in PostgreSQL. È la stessa conversione; :: è la sintassi più corta di PostgreSQL.
Modificare i dati
- Upsert (ON CONFLICT)Upsert in PostgreSQL con INSERT ... ON CONFLICT: DO UPDATE con EXCLUDED, DO NOTHING, update condizionali, contatori e come distinguere le righe inserite da quelle aggiornate.
- RETURNINGRETURNING fa restituire a INSERT, UPDATE, DELETE e MERGE le righe che hanno modificato: id generati, nuovi valori, righe eliminate e quale azione ha eseguito MERGE.
- TransazioniCome funzionano le transazioni in PostgreSQL: BEGIN, COMMIT e ROLLBACK, i savepoint, cosa fa un errore a una transazione aperta e i livelli di isolamento.
JSON e array
- JSONBSalvare e interrogare JSON in PostgreSQL con jsonb: gli operatori -> e ->>, il contenimento con @>, jsonb_set e gli altri aggiornamenti, gli array trasformati in righe, gli indici GIN e json vs jsonb.
- ArrayLe colonne array di PostgreSQL: come dichiararle, indici che partono da 1, come verificare se un array contiene un valore con ANY, @> e &&, unnest e le funzioni sugli array che ti servono.
- string_agg e array_aggstring_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.
Stringhe e date
- Funzioni stringaLe funzioni stringa di PostgreSQL che usi ogni giorno: substring, left e right, || e concat, split_part, position, replace, regexp_replace, trim, lpad e format.
- Funzioni sulle dateLavorare con le date in PostgreSQL: now() e current_date, aggiungere e sottrarre intervalli, date_trunc per raggruppare, extract e age, e la formattazione con to_char.
- generate_seriesgenerate_series restituisce un insieme di righe da un valore iniziale a uno finale: numeri, date e timestamp. Serve a riempire i buchi nei report, generare dati di test e costruire calendari.
Query avanzate
- CTE (WITH)Una common table expression dà un nome a una subquery in una clausola WITH, così la query principale può leggerla come una tabella. WITH RECURSIVE le permette di riferirsi a sé stessa per percorrere alberi e grafi.
- Funzioni finestraLe funzioni finestra calcolano un valore per ogni riga a partire da un gruppo di righe collegate, senza comprimerle: classifiche, totali progressivi, medie mobili e confronti con la riga precedente.
Prestazioni e manutenzione
- IndiciCREATE INDEX crea di default un indice B-tree, così PostgreSQL trova le righe senza leggere tutta la tabella. Gli altri tipi (GIN, GiST, BRIN, hash) coprono JSONB, array, ricerca full-text, intervalli e tabelle molto grandi.
- EXPLAIN ANALYZEEXPLAIN mostra il piano che PostgreSQL sceglie per una query, ed EXPLAIN ANALYZE esegue la query e aggiunge il numero reale di righe e i tempi di ogni passaggio. Impara a leggere l'albero del piano e i nomi dei nodi più comuni.
- Viste e viste materializzateUna vista è una query salvata che viene eseguita ogni volta che la leggi. Una vista materializzata memorizza il risultato della query e cambia solo quando esegui REFRESH MATERIALIZED VIEW.
- VACUUMVACUUM rimuove le versioni morte delle righe che UPDATE e DELETE si lasciano dietro, così il loro spazio può essere riusato. Autovacuum lo esegue per te; questa pagina mostra quando, come regolarlo e quando VACUUM FULL vale il suo lock.
Funzioni e trigger
- Funzioni e procedureCREATE FUNCTION definisce una funzione da chiamare dentro le query, scritta in SQL o in PL/pgSQL. CREATE PROCEDURE definisce una routine da eseguire con CALL, che può fare commit delle transazioni man mano.
- TriggerUn trigger esegue una funzione in automatico quando delle righe vengono inserite, aggiornate o eliminate. In PostgreSQL scrivi una funzione trigger che restituisce trigger, poi la colleghi a una tabella con CREATE TRIGGER.