Vai al contenuto
code-to-code

Apro l'indice…

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

SQL6. Scriverep. 137

Scrivere, esercizi spiegati

Cinque esempi con le tariffe di dicembre

  • 20 minuti di lettura

Il 7 dicembre la Linea Menta compie sessant'anni. Marta Villa, la capostazione di Piazza Query, prepara le promozioni della festa e deve anche ritoccare il listino. Cinque esempi, dal più semplice. Ognuno ha lo stesso ritmo:

  1. Prevedi: leggi il comando e scrivi su un foglio cosa risponderà PostgreSQL. Prima di aprire la risposta.
  2. In italiano: cosa chiede il comando, a parole.
  3. Passo per passo: cosa succede, un pezzo alla volta.
  4. Cosa portiamo a casa.

Per provarli: psql -d sql_scrivere, poi incolla i comandi nell'ordine in cui li trovi: ogni esempio parte da quello che hanno lasciato i precedenti. Se ti perdi, psql -d sql_scrivere -f linea_menta.sql rimette a posto le tabelle della linea (non la tabella promozioni: quella la rifai dall'esempio A).


A · La tabella delle promozioni

Marta vuole una tabella per le promozioni: un nome, la tariffa a cui si applicano, lo sconto in percentuale, da quando a quando valgono, e se sono attive. Regole: il nome non si ripete, lo sconto va da 1 a 100, la tariffa deve esistere, la fine non viene prima dell'inizio, e di default una promozione è attiva.

SQL
DROP TABLE IF EXISTS promozioni;

CREATE TABLE promozioni (
    id          SERIAL PRIMARY KEY,
    nome        TEXT NOT NULL UNIQUE,
    tariffa_id  INTEGER NOT NULL REFERENCES tariffe(id),
    sconto      INTEGER NOT NULL CHECK (sconto BETWEEN 1 AND 100),
    dal         DATE NOT NULL,
    al          DATE NOT NULL,
    attiva      BOOLEAN NOT NULL DEFAULT true,
    CHECK (al >= dal)
);
Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.

Prima volta: DROP TABLE IF EXISTS dice che la tabella non c'è e va avanti, poi la crea.

NOTICE:  table "promozioni" does not exist, skipping
DROP TABLE
CREATE TABLE

Se rilanci solo il CREATE TABLE senza il DROP, la seconda volta:

ERROR:  relation "promozioni" already exists

In italiano. Crea una tabella promozioni con sette colonne. Ogni colonna ha il suo tipo e le sue regole. In fondo, una regola che riguarda due colonne insieme.

Passo per passo.

RigaCosa dice
id SERIAL PRIMARY KEYun numero che si dà da solo, unico, mai vuoto
nome TEXT NOT NULL UNIQUEun testo obbligatorio che non si ripete tra le promozioni
tariffa_id … REFERENCES tariffe(id)un numero che deve essere l'id di una tariffa vera (è la freccia del capitolo Le relazioni)
sconto … CHECK (sconto BETWEEN 1 AND 100)un intero da 1 a 100, estremi compresi
dal, aldue date obbligatorie
attiva … DEFAULT truese non la scrivi, la promozione è attiva
CHECK (al >= dal)in fondo, perché guarda due colonne: la fine non è prima dell'inizio

Cosa portiamo a casa. Una riga per colonna, nome, tipo, regole. Le regole che guardano più colonne vanno dopo l'ultima colonna. E DROP TABLE IF EXISTS in testa rende il file rilanciabile (ma butta le righe che c'erano).


B · Tre promozioni, e le regole che dicono no

Marta inserisce le promozioni della festa. Una sola riga, poi due insieme (con attiva scritta in una e lasciata fuori nell'altra):

SQL
INSERT INTO promozioni (nome, tariffa_id, sconto, dal, al)
VALUES ('Sessant''anni', 1, 50, '2026-12-07', '2026-12-07');

INSERT INTO promozioni (nome, tariffa_id, sconto, dal, al, attiva)
VALUES ('Natale in famiglia', 2, 20, '2026-12-20', '2027-01-06', true),
       ('Saldi di gennaio', 4, 15, '2027-01-07', '2027-01-31', false);

SELECT * FROM promozioni;
Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.

INSERT 0 1, poi INSERT 0 2, poi tre righe. La prima ha attiva a t anche se non l'abbiamo scritta: è il DEFAULT.

INSERT 0 1
INSERT 0 2
 id |        nome        | tariffa_id | sconto |    dal     |     al     | attiva 
----+--------------------+------------+--------+------------+------------+--------
  1 | Sessant'anni       |          1 |     50 | 2026-12-07 | 2026-12-07 | t
  2 | Natale in famiglia |          2 |     20 | 2026-12-20 | 2027-01-06 | t
  3 | Saldi di gennaio   |          4 |     15 | 2027-01-07 | 2027-01-31 | f
(3 rows)

(t e f sono true e false come li scrive psql.)

In italiano. Aggiungi una promozione, poi altre due con un solo comando. Gli id non li scrivi: li dà la tabella.

Ora la tabella si difende. Cinque inserimenti sbagliati, ognuno con un errore diverso:

SQL
INSERT INTO promozioni (nome, tariffa_id, sconto, dal, al)
VALUES ('Sessant''anni', 1, 10, '2026-12-08', '2026-12-09');
ERROR:  duplicate key value violates unique constraint "promozioni_nome_key"
DETAIL:  Key (nome)=(Sessant'anni) already exists.
SQL
INSERT INTO promozioni (nome, tariffa_id, sconto, dal, al)
VALUES ('Tutto gratis', 1, 150, '2026-12-08', '2026-12-09');
ERROR:  new row for relation "promozioni" violates check constraint "promozioni_sconto_check"
DETAIL:  Failing row contains (5, Tutto gratis, 1, 150, 2026-12-08, 2026-12-09, t).
SQL
INSERT INTO promozioni (nome, tariffa_id, sconto, dal, al)
VALUES ('Tariffa fantasma', 9, 10, '2026-12-08', '2026-12-09');
ERROR:  insert or update on table "promozioni" violates foreign key constraint "promozioni_tariffa_id_fkey"
DETAIL:  Key (tariffa_id)=(9) is not present in table "tariffe".
SQL
INSERT INTO promozioni (nome, tariffa_id, sconto, dal, al)
VALUES ('Al contrario', 1, 10, '2026-12-09', '2026-12-08');
ERROR:  new row for relation "promozioni" violates check constraint "promozioni_check"
DETAIL:  Failing row contains (7, Al contrario, 1, 10, 2026-12-09, 2026-12-08, t).
SQL
INSERT INTO promozioni (nome, tariffa_id, sconto, dal)
VALUES ('Senza fine', 1, 10, '2026-12-08');
ERROR:  null value in column "al" of relation "promozioni" violates not-null constraint
DETAIL:  Failing row contains (8, Senza fine, 1, 10, 2026-12-08, null, t).

Passo per passo. Ogni errore dice quale regola (unique, check, foreign key, not-null) e, nel DETAIL, quale valore l'ha rotta. Il nome della regola è il nome della tabella, della colonna e del tipo: promozioni_sconto_check è il CHECK sulla colonna sconto. Il CHECK su due colonne, senza un nome da dare, si chiama solo promozioni_check.

Guarda anche gli id: la riga che fallisce ne mostra uno (5, 7, 8…) ma nessuna riga lo tiene. Gli id degli inserimenti rifiutati sono bruciati.

Un ultimo tentativo, con i valori in un altro ordine delle colonne:

SQL
INSERT INTO promozioni (nome, sconto, tariffa_id, dal, al)
VALUES ('Colonne scambiate', 1, 10, '2026-12-08', '2026-12-09');
ERROR:  insert or update on table "promozioni" violates foreign key constraint "promozioni_tariffa_id_fkey"
DETAIL:  Key (tariffa_id)=(10) is not present in table "tariffe".

Marta voleva tariffa 1 e sconto 10, ha scritto le colonne nell'ordine sbagliato (sconto, tariffa_id) e i valori nell'ordine giusto: il 10 è finito nella colonna della tariffa. Stavolta PostgreSQL se n'è accorto per caso, perché la tariffa 10 non esiste. Con due numeri validi per tutte e due le colonne, per esempio 3 e 4, il comando sarebbe passato e la promozione sarebbe stata sbagliata in silenzio.

Cosa portiamo a casa. I vincoli dicono no in modo preciso: leggi l'errore. L'elenco delle colonne e quello dei valori camminano insieme, uno a uno: se non li rileggi, nessuno ti avvisa.


C · Il listino cambia, ma non il passato

Dal 1° dicembre il biglietto ordinario costa 2,70 euro invece di 2,50. L'abitudine: prima guardi la riga che vuoi cambiare.

SQL
SELECT id, nome, prezzo FROM tariffe WHERE nome = 'ordinario';
 id |   nome    | prezzo 
----+-----------+--------
  1 | ordinario |   2.50
(1 row)

Una riga sola: il WHERE è giusto. Ora lo stesso WHERE dentro l'UPDATE:

SQL
UPDATE tariffe SET prezzo = 2.70 WHERE nome = 'ordinario';
SELECT id, nome, prezzo FROM tariffe ORDER BY id;
Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.

UPDATE 1, poi il listino con il solo ordinario cambiato.

UPDATE 1
 id |    nome     | prezzo 
----+-------------+--------
  1 | ordinario   |   2.70
  2 | giornaliero |   8.00
  3 | ridotto     |   1.30
  4 | settimanale |  18.00
(4 rows)

In italiano. Tra le tariffe, nella riga che si chiama «ordinario», metti il prezzo a 2,70.

Passo per passo.

PassoCosa faCosa resta
UPDATE tariffesceglie la tabella4 righe
WHERE nome = 'ordinario'sceglie quali righe1 riga
SET prezzo = 2.70cambia quel valore in quella rigala riga 1 ha 2.70, le altre 3 non si toccano

Il passato, invece, non cambia. Le vendite hanno il loro prezzo, scritto nel giorno della vendita:

SQL
SELECT SUM(prezzo_pagato) FROM vendite;
SELECT tariffa_id, data, prezzo_pagato FROM vendite WHERE tariffa_id = 1 AND data = '2026-11-16';
  sum  
-------
 86.20
(1 row)

 tariffa_id |    data    | prezzo_pagato 
------------+------------+---------------
          1 | 2026-11-16 |          2.50
          1 | 2026-11-16 |          2.50
(2 rows)

L'incasso è sempre 86,20 e i biglietti di novembre restano a 2,50: vendite.prezzo_pagato è una fotografia del prezzo di quel giorno, non un rimando al listino. Se non fosse così, cambiare il listino riscriverebbe tutti gli incassi passati.

Ora l'errore che si fa davvero. Marta vuole alzare di 20 centesimi il giornaliero e scrive in fretta:

SQL
UPDATE tariffe SET prezzo = prezzo + 0.20;
Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.

Nessun errore. Ma non dice UPDATE 1:

UPDATE 4

Il WHERE è sparito: tutte e quattro le tariffe sono salite di 20 centesimi.

SQL
SELECT id, nome, prezzo FROM tariffe ORDER BY id;
 id |    nome     | prezzo 
----+-------------+--------
  1 | ordinario   |   2.90
  2 | giornaliero |   8.20
  3 | ridotto     |   1.50
  4 | settimanale |  18.20
(4 rows)

Come si rimedia? Con un comando di segno contrario, perché sappiamo esattamente cosa è stato fatto a tutte le righe:

SQL
UPDATE tariffe SET prezzo = prezzo - 0.20;
UPDATE 4

Il listino è tornato 2,70, 8,00, 1,30, 18,00. Non sempre si può: se avessi scritto SET prezzo = 1.50 su tutte le righe, i vecchi prezzi sarebbero persi per sempre. Per questo il WHERE si controlla prima, con una SELECT, e il numero dopo l'UPDATE si legge sempre.

Cosa portiamo a casa. SELECT con il WHERE, poi UPDATE con lo stesso WHERE, poi UPDATE 1. Un numero diverso da quello atteso è il campanello. E il prezzo pagato è una fotografia: cambiare il listino non cambia il passato (ci torni in Disegnare un database).


D · Togliere, e le righe che puntano

Marta vuole togliere la tariffa settimanale, che non ha mai venduto nessuno.

SQL
DELETE FROM tariffe WHERE nome = 'settimanale';
Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.

Un errore: la promozione «Saldi di gennaio» punta ancora a quella tariffa.

ERROR:  update or delete on table "tariffe" violates foreign key constraint "promozioni_tariffa_id_fkey" on table "promozioni"
DETAIL:  Key (id)=(4) is still referenced from table "promozioni".

In italiano. Togli la riga della settimanale. PostgreSQL rifiuta: una riga della tabella promozioni ha ancora tariffa_id = 4, e la freccia non può puntare nel vuoto. Prima si toglie chi punta, poi la riga:

SQL
DELETE FROM promozioni WHERE nome = 'Saldi di gennaio';
DELETE FROM tariffe WHERE nome = 'settimanale';
DELETE 1
DELETE 1

E l'ordinario? Ha venduto biglietti, e lo ricorda:

SQL
DELETE FROM tariffe WHERE nome = 'ordinario';
ERROR:  update or delete on table "tariffe" violates foreign key constraint "vendite_tariffa_id_fkey" on table "vendite"
DETAIL:  Key (id)=(1) is still referenced from table "vendite".

Qui la chiave esterna è un'amica: senza, le vendite di novembre non avrebbero più una tariffa a cui riferirsi.

Passo per passo.

ComandoRisultatoPerché
DELETE … 'settimanale' (1ª volta)errorela promozione 3 punta alla tariffa 4
DELETE … promozioni WHERE nome = …DELETE 1nessuno punta alle promozioni
DELETE … 'settimanale' (2ª volta)DELETE 1ora nessuna riga punta alla tariffa 4
DELETE … 'ordinario'erroreci sono vendite con tariffa_id = 1

Ora lo scherzo del WHERE dimenticato, su una tabella che puoi permetterti di svuotare:

SQL
DELETE FROM promozioni;
SELECT * FROM promozioni;
DELETE 2
 id | nome | tariffa_id | sconto | dal | al | attiva 
----+------+------------+--------+-----+----+--------
(0 rows)

DELETE 2: le due promozioni rimaste sono sparite. La tabella c'è ancora, con le sue colonne e le sue regole, ma vuota. Su tariffe lo stesso comando non sarebbe passato (le vendite puntano alle tariffe), ma su una tabella a cui nessuno punta, passa.

Cosa portiamo a casa. DELETE FROM … WHERE …, e poi si legge DELETE 1. Una riga a cui altre righe puntano non si cancella: prima quelle che puntano. E DELETE senza WHERE non chiede il permesso.


E · RETURNING: dimmi cosa hai fatto

Marta rimette la promozione dei sessant'anni e vuole sapere subito che numero ha preso. Poi la modifica, poi la toglie, sempre guardando cosa succede.

SQL
INSERT INTO promozioni (nome, tariffa_id, sconto, dal, al)
VALUES ('Sessant''anni', 1, 50, '2026-12-07', '2026-12-07')
RETURNING id, nome;
Prevedi Scrivi su un foglio cosa ti aspetti, poi apri.

Una riga con l'id e il nome. Ma l'id non è 1: i numeri bruciati dagli inserimenti rifiutati e dalle righe cancellate non tornano.

 id |     nome     
----+--------------
 10 | Sessant'anni
(1 row)

INSERT 0 1
SQL
UPDATE promozioni SET sconto = 40 WHERE nome = 'Sessant''anni' RETURNING id, nome, sconto;
UPDATE promozioni SET sconto = 40 WHERE nome = 'Sessanta' RETURNING id, nome, sconto;
DELETE FROM promozioni WHERE sconto = 40 RETURNING id, nome;
 id |     nome     | sconto 
----+--------------+--------
 10 | Sessant'anni |     40
(1 row)

UPDATE 1
 id | nome | sconto 
----+------+--------
(0 rows)

UPDATE 0
 id |     nome     
----+--------------
 10 | Sessant'anni
(1 row)

DELETE 1

In italiano. Fai il comando e fatti restituire le colonne delle righe toccate, come una SELECT.

Passo per passo.

ComandoCosa mostra RETURNING
INSERT … RETURNING id, nomela riga nata, con l'id che le ha dato la tabella
UPDATE … 'Sessant''anni' RETURNING …la riga dopo il cambio: sconto 40
UPDATE … 'Sessanta' RETURNING …niente righe: il nome era sbagliato (una lettera di meno) e UPDATE 0 lo conferma
DELETE … RETURNING id, nomela riga appena tolta

Il secondo UPDATE è il più istruttivo: non dà un errore, dà zero righe. Una scrittura che non trova niente non è un errore per PostgreSQL, ma lo è per te: guarda sempre il numero.

Cosa portiamo a casa. RETURNING ti mostra subito cosa è stato scritto, cambiato o tolto: l'id appena nato, la riga dopo la modifica, la lista di cosa sparisce. E un comando che tocca zero righe non è mai un errore per PostgreSQL: lo devi vedere tu.