Tabelle, tipi e psql
Dove vivono i dati, e come si guardano
- 12 minuti di lettura
Ernesto Pozzi, il capo deposito, ha un quaderno a righe. Una riga per treno: il codice, il numero di vagoni, la data dell'ultima revisione. Funziona, finché lo usa lui.
Poi arriva Ivo, il manutentore, e scrive nel quaderno la revisione di un treno mentre Ernesto sta cancellando quella vecchia. Poi qualcuno scrive «sei» al posto di 6. Poi due righe dicono cose diverse sullo stesso treno.
Un database è il quaderno di Ernesto con tre cose in più: lo possono usare in tanti insieme, ogni dato ha un tipo (un numero deve restare un numero) e il database rifiuta i dati sbagliati prima che entrino. Non è un trucco da programmatori: è esattamente quello che chiediamo a un quaderno condiviso.
Per esempio, il listino dei biglietti della Linea Menta non può avere un prezzo negativo. Se ci provi, PostgreSQL non scrive niente:
ERROR: new row for relation "tariffe" violates check constraint "tariffe_prezzo_check"
DETAIL: Failing row contains (5, prova, -1.00).Non devi capire adesso come si scrive quel comando (è il capitolo Scrivere). Guarda solo che il rifiuto c'è, ed è scritto in chiaro.
Il database che useremo è PostgreSQL, e il linguaggio con cui gli si parla è SQL. In questo capitolo impari a guardare cosa c'è dentro.
Una tabella è una lista di dizionari
Lo sai già. Nel capitolo I dizionari una lista di dizionari con le stesse chiavi era un elenco di treni:
treni = [
{"codice": "M14", "vagoni": 4},
{"codice": "M12", "vagoni": 6},
]
print(treni[1]["vagoni"])6Una tabella è la stessa cosa, con una regola in più: tutti i dizionari hanno le stesse chiavi e ogni chiave ha un tipo. Le chiavi si chiamano colonne, ogni dizionario si chiama riga.
Il disegno
tabella treni
colonne
┌────┬────────┬───────┬────────┐
│ id │ codice │ tipo │ vagoni │ ...
├────┼────────┼───────┼────────┤
riga ─▶ │ 1 │ M14 │ metro │ 4 │
riga ─▶ │ 2 │ M12 │ metro │ 6 │
riga ─▶ │ 3 │ T28 │ tram │ 2 │
└────┴────────┴───────┴────────┘- La colonna dice cosa si sa di ogni treno, e di che tipo è:
codiceè un testo,vagoniè un numero. - La riga è un treno, con tutte le sue colonne.
- Il database ha tante tabelle:
treni,stazioni,personale… Ognuna parla di una cosa sola.
I tipi che servono
Ogni colonna ha un tipo. Ne bastano pochi, e li trovi tutti nel database della Linea Menta.
| Tipo | Cosa tiene | Un esempio |
|---|---|---|
INTEGER | Numeri interi | vagoni: 4 |
NUMERIC(5,2) | Numeri con la virgola esatti: cinque cifre in tutto, due dopo la virgola. Per i soldi | prezzo: 2.50 |
TEXT | Testo, di qualunque lunghezza | codice: M14 |
DATE | Un giorno | inaugurata_il: 1966-12-07 |
TIME | Un'ora del giorno | partenza: 05:42:00 |
TIMESTAMP | Giorno e ora insieme | 2026-11-16 05:42:00 |
BOOLEAN | Vero o falso | aperta: t (true) o f (false) |
SERIAL | Un intero che si numera da solo: 1, 2, 3… | id |
TIMESTAMP non compare nel database del libro: lo nominiamo e basta.
I soldi non sono mai un numero con la virgola «normale». Python te lo mostra:
print(0.1 + 0.2)0.30000000000000004NUMERIC invece è esatto, come il resto della cassa:
SELECT 0.1 + 0.2; ?column?
----------
0.3
(1 row)La chiave primaria
Come si distingue una riga da un'altra? Non dal nome: ci sono due Colombo, Sergio e Anna. Serve una colonna che non si ripete mai.
La chiave primaria è la colonna (o le colonne) che identifica una riga, e ha tre regole:
- è unica: due righe non hanno la stessa;
- non è mai vuota;
- non cambia nel tempo.
Nelle tabelle della Linea Menta la chiave primaria si chiama quasi sempre id, ed è un SERIAL: ci pensa il database a dare 1, 2, 3. Nel personale, per comodità, l'id coincide con il numero del badge: B-07 è la riga 7.
SELECT id, badge FROM personale WHERE id < 4; id | badge
----+-------
1 | B-01
2 | B-02
3 | B-03
(3 rows)(Il WHERE id < 4 è del prossimo capitolo: per ora leggilo come «solo le prime tre».)
E se qualcuno prova a dare a una riga nuova un id che esiste già? Il database dice no:
ERROR: duplicate key value violates unique constraint "tariffe_pkey"
DETAIL: Key (id)=(1) already exists.A volte una riga si riconosce solo con due colonne insieme. La tabella personale_patenti dice quale patente ha preso ogni persona: Sergio (4) ha la patente metro (1) e la tram (2).
personale_id | patente_id | presa_il
--------------+------------+------------
4 | 1 | 2012-03-01
4 | 2 | 2014-04-12
11 | 1 | 2014-12-01
...Né personale_id né patente_id sono unici da soli (il 4 compare due volte, il 1 pure). La coppia sì. Si chiama chiave primaria doppia, e \d la scrive così: PRIMARY KEY, btree (personale_id, patente_id).
La casella vuota
Guarda i treni M20 e T31: nella colonna ultima_revisione non c'è niente. Non è uno zero e non è un testo vuoto: il valore non si sa. Si chiama NULL, come il None di Python, e psql lo mostra come una casella vuota. Per ora ti basta riconoscerlo: come si cerca e come si conta lo vedi in Leggere.
psql, la finestra sul database
PostgreSQL gira in sottofondo. psql è il programma da terminale con cui gli parli. Come si accende PostgreSQL, i ruoli, il socket: PostgreSQL e psql. Qui diamo per scontato che sia acceso (pg_isready dice accepting connections) e che il database sia stato creato e caricato.
Una volta sola, per creare il database e riempirlo con il file linea_menta.sql dello zip:
createdb sql_tabelle_e_psql # crea un database vuoto
psql -d sql_tabelle_e_psql -f linea_menta.sql # carica il file: tabelle e dati-d dice a quale database ti colleghi, -f dice quale file eseguire. L'output sono le risposte di PostgreSQL a ogni comando del file:
SET
DROP TABLE
CREATE TABLE
INSERT 0 10
CREATE TABLE
INSERT 0 8
CREATE TABLE
INSERT 0 16
CREATE TABLE
INSERT 0 3
CREATE TABLE
INSERT 0 9
CREATE TABLE
INSERT 0 20
CREATE TABLE
INSERT 0 16
CREATE TABLE
INSERT 0 4
CREATE TABLE
INSERT 0 24Nove CREATE TABLE (le tabelle), nove INSERT (le righe: 10 stazioni, 8 treni, 16 persone…). Il file si può rilanciare quando vuoi: ricomincia da capo.
Per entrare e guardarti intorno:
psql -d sql_tabelle_e_psqlIl prompt cambia: sql_tabelle_e_psql=#. Vuol dire: sono dentro, dimmi.
I comandi che ti servono
Hanno la barra rovesciata davanti e non sono SQL: sono comandi di psql, non vogliono il punto e virgola.
| Scrivi | Cosa fa |
|---|---|
\dt | L'elenco delle tabelle |
\d treni | La descrizione di una tabella: colonne, tipi, chiave primaria |
\x auto | Se una riga è troppo larga per lo schermo, la mostra in verticale |
\q | Esci |
| freccia su | Ripete il comando di prima, da modificare |
Il database vero invece capisce SQL, e ogni comando SQL finisce con un ;. Fino al punto e virgola psql non fa niente: aspetta.
sql_tabelle_e_psql=# SELECT codice, vagoni FROM treni
sql_tabelle_e_psql-# ;Il prompt -# (trattino al posto dell'uguale) vuol dire: «il comando non è finito, continua». Succede quando dimentichi il ;. Scrivilo e premi Invio, oppure Ctrl+C per ricominciare.
Ecco \dt:
List of relations
Schema | Name | Type | Owner
--------+-------------------+-------+-------------
public | corse | table | davidelista
public | oggetti_smarriti | table | davidelista
public | patenti | table | davidelista
public | personale | table | davidelista
public | personale_patenti | table | davidelista
public | stazioni | table | davidelista
public | tariffe | table | davidelista
public | treni | table | davidelista
public | vendite | table | davidelista
(9 rows)Nove tabelle. L'ultima colonna, Owner, è il tuo nome utente: sul tuo computer sarà diverso.
E \d treni:
Table "public.treni"
Column | Type | Collation | Nullable | Default
------------------+---------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('treni_id_seq'::regclass)
codice | text | | not null |
tipo | text | | not null |
vagoni | integer | | not null |
in_servizio_dal | date | | not null |
ultima_revisione | date | | |
Indexes:
"treni_pkey" PRIMARY KEY, btree (id)
"treni_codice_key" UNIQUE CONSTRAINT, btree (codice)
Referenced by:
TABLE "corse" CONSTRAINT "corse_treno_id_fkey" FOREIGN KEY (treno_id) REFERENCES treni(id)Come si legge:
- Column e Type: le colonne e il loro tipo. L'
idèintegercon unDefaultche si numera da solo: è ilSERIAL. - Nullable:
not nullvuol dire «obbligatoria». Soloultima_revisionepuò restare vuota. - Indexes: la riga
PRIMARY KEY, btree (id)ti dice qual è la chiave primaria. Quella conUNIQUEdice che anchecodicenon si ripete.btreeè il modo in cui il database la tiene in ordine: ignoralo. - Referenced by: le tabelle che puntano a questa. Le vediamo nel capitolo Le relazioni.
La prima domanda
La domanda più semplice: dammi tutto quello che c'è in una tabella.
SELECT * FROM treni; id | codice | tipo | vagoni | in_servizio_dal | ultima_revisione
----+--------+-------+--------+-----------------+------------------
1 | M14 | metro | 4 | 2009-02-01 | 2026-09-30
2 | M12 | metro | 6 | 2005-05-10 | 2026-06-15
3 | T28 | tram | 2 | 2015-07-01 | 2025-11-20
4 | M16 | metro | 6 | 2012-10-01 | 2026-10-12
5 | T05 | tram | 1 | 1998-03-15 | 2024-04-02
6 | M18 | metro | 4 | 2019-01-20 | 2026-03-03
7 | M20 | metro | 5 | 2025-12-01 |
8 | T31 | tram | 2 | 2026-11-02 |
(8 rows)Si legge: «seleziona tutte le colonne (*) dalla tabella treni». Le ultime due caselle di ultima_revisione sono vuote: sono i NULL.
Se ti servono solo alcune colonne, le elenchi tu, nell'ordine in cui le vuoi:
SELECT vagoni, codice FROM treni; vagoni | codice
--------+--------
4 | M14
6 | M12
2 | T28
6 | M16
1 | T05
4 | M18
5 | M20
2 | T31
(8 rows)Le righe sono le stesse, le colonne sono due e in quest'ordine: quello che hai scritto dopo SELECT. Le maiuscole delle parole chiave (SELECT, FROM) sono un'abitudine, non un obbligo: ma in questo libro le scriviamo così, per ritrovarle a colpo d'occhio.
Leggere un CREATE TABLE
Dietro \d c'è il comando che ha costruito la tabella. Non lo scrivi ancora (è il capitolo Scrivere), ma lo leggi: sta dentro linea_menta.sql.
CREATE TABLE treni (
id SERIAL PRIMARY KEY,
codice TEXT NOT NULL UNIQUE,
tipo TEXT NOT NULL,
vagoni INTEGER NOT NULL,
in_servizio_dal DATE NOT NULL,
ultima_revisione DATE
);Ogni riga tra le parentesi è una colonna: il nome, il tipo, poi le regole.
| Parola | Vuol dire |
|---|---|
SERIAL PRIMARY KEY | Si numera da solo, ed è la chiave primaria |
NOT NULL | Obbligatoria: non può restare vuota |
UNIQUE | Non si ripete: due treni non hanno lo stesso codice |
| (niente) | Può restare vuota: ultima_revisione di un treno mai revisionato |
È la stessa informazione di \d, scritta dall'altra parte.
Cosa viene dopo
Ora sai dove stanno i dati e come guardarli. Filtrare le righe, metterle in ordine, cercare le caselle vuote: Leggere. Le frecce tra una tabella e l'altra (Referenced by): Le relazioni.
Ricorda
- Una tabella è una lista di dizionari con le stesse chiavi: le colonne sono le chiavi, le righe sono i dizionari.
- Ogni colonna ha un tipo:
INTEGER,NUMERIC(5,2)per i soldi,TEXT,DATE,TIME,BOOLEAN, eSERIALper l'idche si numera da solo. - La chiave primaria è unica, mai vuota e non cambia. A volte è fatta da due colonne.
- Una casella vuota è
NULL: il valore non si sa. - In psql:
\dtle tabelle,\d nomele colonne,\qesci. I comandi SQL finiscono con;: se il prompt diventa-#, manca. SELECT * FROM treni;dà tutto;SELECT codice, vagoni FROM treni;dà solo quelle colonne, in quell'ordine.