Vai al contenuto
code-to-code

Apro l'indice…

↑ ↓ per scegliere, Invio per aprire, Esc per chiudere

SQL1. Tabelle, tipi e psqlp. 1

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:

Python
treni = [
    {"codice": "M14", "vagoni": 4},
    {"codice": "M12", "vagoni": 6},
]
print(treni[1]["vagoni"])
6

Una 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.

TipoCosa tieneUn esempio
INTEGERNumeri interivagoni: 4
NUMERIC(5,2)Numeri con la virgola esatti: cinque cifre in tutto, due dopo la virgola. Per i soldiprezzo: 2.50
TEXTTesto, di qualunque lunghezzacodice: M14
DATEUn giornoinaugurata_il: 1966-12-07
TIMEUn'ora del giornopartenza: 05:42:00
TIMESTAMPGiorno e ora insieme2026-11-16 05:42:00
BOOLEANVero o falsoaperta: t (true) o f (false)
SERIALUn 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:

Python
print(0.1 + 0.2)
0.30000000000000004

NUMERIC invece è esatto, come il resto della cassa:

SQL
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:

  1. è unica: due righe non hanno la stessa;
  2. non è mai vuota;
  3. 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.

SQL
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:

Terminale
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 24

Nove 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:

Terminale
psql -d sql_tabelle_e_psql

Il 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.

ScriviCosa fa
\dtL'elenco delle tabelle
\d treniLa descrizione di una tabella: colonne, tipi, chiave primaria
\x autoSe una riga è troppo larga per lo schermo, la mostra in verticale
\qEsci
freccia suRipete 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 è integer con un Default che si numera da solo: è il SERIAL.
  • Nullable: not null vuol dire «obbligatoria». Solo ultima_revisione può restare vuota.
  • Indexes: la riga PRIMARY KEY, btree (id) ti dice qual è la chiave primaria. Quella con UNIQUE dice che anche codice non 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.

SQL
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:

SQL
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.

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.

ParolaVuol dire
SERIAL PRIMARY KEYSi numera da solo, ed è la chiave primaria
NOT NULLObbligatoria: non può restare vuota
UNIQUENon 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, e SERIAL per l'id che 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: \dt le tabelle, \d nome le colonne, \q esci. 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.