Accueil » Formations » Fondamentaux en informatique » Bases de données et SQL avec PostgreSQL » TP03 – Insérer, modifier et importer des données en transaction

TP03 – Insérer, modifier et importer des données en transaction

← Bases de données et SQL avec PostgreSQL

// travaux pratiques · Fondamentaux en informatique

TP03 - Insérer, modifier et importer des données en transaction

  • TP 3 / 7
  • 7 étapes
  • ≈ 45 min
  • Accès libre

Objectif : alimenter la base parc avec INSERT, corriger des données avec UPDATE, protéger une opération risquée dans une transaction annulable et importer un fichier CSV avec la méta-commande \copy.

Contexte

Les données d’une base arrivent rarement à la main : elles viennent d’un export d’un ancien outil, d’un tableur tenu par une équipe, d’un script d’inventaire ou d’une application. Un administrateur doit pourtant savoir insérer, corriger et supprimer des lignes en SQL, ne serait-ce que pour réparer une donnée erronée en production. Le danger principal est connu de tous : un UPDATE ou un DELETE lancé sans clause WHERE modifie toute la table. Les transactions sont le filet de sécurité qui permet de vérifier avant de valider.

À la fin de ce TP, la base contiendra 3 sites, 10 équipements et 12 interventions, et vous saurez annuler une modification avant qu’elle ne devienne définitive, ainsi qu’importer un CSV depuis votre poste sans droits particuliers sur le serveur.

Prérequis : TP02 terminé (base parc, rôle parc_admin et fichier ~/.pgpass).

Durée estimée : 45 minutes.

Actions à réaliser

1. Repartir de tables vides

Les essais du TP02 ont consommé des numéros d’identité, même pour les insertions refusées. TRUNCATE vide les tables bien plus vite qu’un DELETE et l’option RESTART IDENTITY remet les compteurs à 1. Les trois tables doivent être citées ensemble, car elles sont liées par des clés étrangères.

psql -h localhost -U parc_admin -d parc
TRUNCATE sites, equipements, interventions RESTART IDENTITY;

Résultat attendu : le message TRUNCATE TABLE.

2. Insérer plusieurs lignes et récupérer les identifiants

Une seule instruction INSERT peut insérer plusieurs lignes. La clause RETURNING, propre à PostgreSQL, renvoie les valeurs générées par la base, ici les identifiants : inutile de faire une requête supplémentaire pour les connaître.

INSERT INTO sites (nom, ville) VALUES
  ('Siège', 'Nice'),
  ('Agence Sophia', 'Valbonne'),
  ('Entrepôt', 'Carros')
RETURNING id, nom;

Résultat attendu : un tableau de trois lignes avec les identifiants 1, 2 et 3, suivi de INSERT 0 3.

3. Insérer les équipements

On fournit explicitement la date de mise en service ; pour les colonnes omises (actif), la valeur par défaut s’applique. Les adresses sont saisies sous forme de texte et converties en inet par la base, qui les vérifie au passage.

INSERT INTO equipements (nom_hote, categorie, site_id, adresse_ip, mise_en_service) VALUES
  ('srv-ad01',     'serveur',    1, '192.168.10.10', '2022-03-01'),
  ('srv-fic01',    'serveur',    1, '192.168.10.11', '2021-09-15'),
  ('srv-sup01',    'serveur',    1, '192.168.10.12', '2024-01-10'),
  ('fw-siege',     'pare-feu',   1, '192.168.10.1',  '2023-05-20'),
  ('sw-siege-01',  'switch',     1, '192.168.10.2',  '2020-11-02'),
  ('pc-compta-01', 'poste',      1, '192.168.20.31', '2023-02-14'),
  ('fw-sophia',    'pare-feu',   2, '192.168.30.1',  '2024-06-03'),
  ('sw-sophia-01', 'switch',     2, '192.168.30.2',  '2024-06-03'),
  ('pc-sophia-01', 'poste',      2, '192.168.30.21', '2025-01-08'),
  ('imp-entrepot', 'imprimante', 3, '192.168.40.50', '2019-04-25');

Résultat attendu : INSERT 0 10. SELECT id, nom_hote, actif FROM equipements; affiche les identifiants 1 à 10, tous avec actif à t (vrai).

4. Corriger une donnée avec UPDATE

L’imprimante de l’entrepôt est retirée du service : on la désactive au lieu de la supprimer, pour garder son historique. Écrivez toujours la clause WHERE en premier et vérifiez-la avec un SELECT avant de lancer la modification.

SELECT id, nom_hote FROM equipements WHERE nom_hote = 'imp-entrepot';
UPDATE equipements SET actif = false WHERE nom_hote = 'imp-entrepot'
RETURNING id, nom_hote, actif;

Résultat attendu : le SELECT renvoie une seule ligne (id 10), puis UPDATE 1 avec la ligne modifiée et actif à f. Le nombre affiché après UPDATE est votre premier contrôle : s’il ne correspond pas à ce que vous attendiez, quelque chose ne va pas.

5. Se protéger avec une transaction

Par défaut, psql valide chaque instruction dès qu’elle se termine (mode autocommit). Entre BEGIN et COMMIT, les modifications restent provisoires et invisibles pour les autres sessions ; ROLLBACK les annule entièrement. Simulez l’erreur classique : un DELETE sans WHERE.

BEGIN;
DELETE FROM equipements;
SELECT count(*) FROM equipements;
ROLLBACK;
SELECT count(*) FROM equipements;

Résultat attendu : pendant la transaction, l’invite devient parc=*> (l’astérisque signale une transaction ouverte) et le comptage renvoie 0 ; après ROLLBACK, il renvoie de nouveau 10. Le DELETE a été accepté car aucune intervention n’existe encore ; avec des interventions, la suppression les aurait emportées par la cascade définie au TP02.

6. Préparer le fichier CSV des interventions

Le fichier est créé sur la machine où s’exécute psql (ici pg01, mais ce pourrait être votre poste). Quittez psql (\q), placez-vous dans votre répertoire personnel et créez le fichier. Les heures portent leur décalage horaire : +02 avant le passage à l’heure d’hiver du 25 octobre 2026, +01 ensuite.

cd ~
cat > interventions.csv <<'EOF'
equipement_id,date_intervention,technicien,description,duree_min
1,2026-10-02 09:15:00+02,amina,Mises à jour mensuelles,45
2,2026-10-02 10:30:00+02,amina,Mises à jour mensuelles,40
4,2026-10-06 14:00:00+02,karim,Mise à jour du firmware,60
1,2026-10-14 08:45:00+02,karim,Extension du disque système,30
5,2026-10-15 16:20:00+02,lucas,Remplacement d'un module SFP,25
7,2026-10-21 11:00:00+02,lucas,Ajout d'une règle de filtrage,20
9,2026-10-23 09:30:00+02,sophie,Réinstallation du poste,120
10,2026-10-28 15:10:00+01,sophie,Changement du toner,15
2,2026-11-03 09:00:00+01,amina,Mises à jour mensuelles,35
1,2026-11-03 09:50:00+01,amina,Mises à jour mensuelles,50
4,2026-11-12 18:30:00+01,karim,Renouvellement du certificat VPN,40
9,2026-11-17 10:15:00+01,lucas,Remplacement de l'écran,30
EOF
wc -l interventions.csv

Résultat attendu : wc compte 13 lignes (l’en-tête et 12 interventions). Les apostrophes ne posent aucun problème dans un CSV ; seules les virgules et les guillemets dans un champ imposeraient de l’entourer de guillemets doubles.

7. Importer le CSV avec \copy

L’instruction SQL COPY … FROM 'fichier' lit le fichier sur le serveur, avec les droits du compte postgres, et demande des privilèges élevés. La méta-commande \copy de psql lit le fichier côté client, avec vos propres droits, et l’envoie au serveur : c’est la bonne méthode pour un utilisateur ordinaire. L’option HEADER ignore la première ligne.

psql -h localhost -U parc_admin -d parc
\copy interventions (equipement_id, date_intervention, technicien, description, duree_min) FROM 'interventions.csv' WITH (FORMAT csv, HEADER)
SELECT count(*), sum(duree_min) FROM interventions;

Résultat attendu : COPY 12, puis un comptage de 12 lignes pour un total de 510 minutes. Un import est atomique : si une seule ligne viole une contrainte, aucune ligne n’est insérée.

Vérification

Exécutez SELECT (SELECT count(*) FROM sites) AS sites, (SELECT count(*) FROM equipements) AS equipements, (SELECT count(*) FROM interventions) AS interventions;. Le TP est réussi si vous obtenez 3, 10 et 12, si l’équipement imp-entrepot a actif à faux, et si vous avez constaté que le ROLLBACK de l’étape 5 a bien restauré les dix équipements.

Problèmes fréquents

  • « cannot truncate a table referenced in a foreign key constraint » : une seule table a été citée dans TRUNCATE. Citez les trois tables dans la même commande.
  • « interventions.csv: No such file or directory » : psql cherche un chemin relatif à partir du répertoire où il a été lancé. Relancez psql depuis ~ ou indiquez le chemin absolu du fichier.
  • « insert or update on table “interventions” violates foreign key constraint » pendant l’import : un equipement_id du fichier n’existe pas, souvent parce que l’étape 1 n’a pas remis les compteurs à zéro. Vérifiez les identifiants avec SELECT id, nom_hote FROM equipements;.
  • « current transaction is aborted, commands ignored until end of transaction block » : une erreur s’est produite dans une transaction ouverte ; toutes les commandes suivantes sont refusées. Terminez par ROLLBACK; et recommencez.

Bonnes pratiques et sécurité

  • Avant toute correction manuelle en production, ouvrez une transaction, exécutez la modification, contrôlez le nombre de lignes touchées, puis seulement COMMIT.
  • Ne laissez pas une transaction ouverte : elle retient des verrous et empêche le nettoyage des anciennes versions de lignes (voir le chapitre sur MVCC du cours).
  • Préférez \copy à COPY pour les imports ponctuels : il n’exige ni droit de superutilisateur ni accès aux fichiers du serveur.
  • Supprimez ou protégez les fichiers d’import après usage : un export d’inventaire ou de comptes contient souvent des informations sensibles.

Questions de contrôle

1. Quelle différence pratique entre COPY … FROM et \copy … FROM ?

Voir la réponse

COPY lit un fichier situé sur le serveur avec les droits du processus serveur ; \copy est exécuté par psql, lit le fichier sur la machine du client avec les droits de l’utilisateur et transmet les données au serveur.

2. Que signifie l’invite parc=*> ?

Voir la réponse

Qu’une transaction est ouverte dans la base parc : les modifications ne seront définitives qu’après COMMIT.

3. Pourquoi désactiver un équipement plutôt que le supprimer ?

Voir la réponse

Parce que la suppression déclencherait la cascade sur ses interventions et ferait disparaître l’historique, utile pour l’audit et les statistiques.

Pour aller plus loin

Les imports réguliers (export nocturne d’un outil d’inventaire, par exemple) s’automatisent avec un petit script shell appelant psql : les bases sont présentées dans le cours Scripting Shell – Découverte des fondamentaux. Sur de gros volumes, COPY reste la méthode d’insertion la plus rapide de PostgreSQL. Conservez les données : le TP04 les interroge.

// formation sur mesure

Former votre équipe sur ce sujet ?

Azur Secure anime des sessions intra-entreprise, à distance ou dans vos locaux sur la Côte d'Azur, adaptées au niveau de vos équipes.

Demander un programme
Défiler vers le haut