Tutoriel PostgreSQL avec exemples exécutables
Un tutoriel PostgreSQL gratuit avec des requêtes qui s'exécutent dans la page : types de données, CREATE TABLE, upserts, JSONB, dates, CTE, fonctions de fenêtrage, index et VACUUM. Modifiez une requête et relancez-la.
Commencer le coursPremiers pas
- PostgreSQL vs MySQLPostgreSQL et MySQL sont deux bases de données relationnelles gratuites. PostgreSQL a le SQL le plus riche, JSONB et le DDL transactionnel ; MySQL est plus simple à exploiter là où il est déjà le choix par défaut.
- PostgreSQL vs SQL ServerPostgreSQL est gratuit et open source ; SQL Server est la base de données commerciale de Microsoft, avec T-SQL et des outils centrés sur Windows. Voici ce qui distingue leur SQL, leur JSON, leurs verrous et leurs licences.
- PostgreSQL vs MongoDBPostgreSQL stocke des lignes dans des tables au schéma fixe et peut garder des documents JSON dans des colonnes jsonb ; MongoDB stocke des documents au schéma souple dans des collections et les répartit sur plusieurs serveurs.
- Lister bases et tablesDans psql, \l liste les bases de données, \c passe à l'une d'elles et \dt liste ses tables. En SQL, interrogez pg_database, information_schema.tables et information_schema.columns.
Tables et types
- Types de donnéesLes types de données PostgreSQL que vous utiliserez pour presque toutes vos colonnes : entiers, numeric, text, boolean, timestamptz, uuid, jsonb, tableaux et enums, et quand choisir chacun.
- CREATE TABLECREATE TABLE définit les colonnes, les types et les contraintes d'une table. Clés identity, NOT NULL, DEFAULT, UNIQUE, CHECK, IF NOT EXISTS, CREATE TABLE AS et LIKE.
- ALTER TABLEALTER TABLE modifie une table existante : ajouter, renommer et supprimer des colonnes, changer le type d'une colonne avec USING, définir des valeurs par défaut et NOT NULL, et ajouter des contraintes.
- Clés primairesComment définir une clé primaire dans PostgreSQL et comment la générer : colonnes identity, l'ancien SERIAL et les UUID avec gen_random_uuid(). Aussi les clés composites et la remise à niveau d'une séquence.
- Clés étrangèresUne clé étrangère n'accepte dans une colonne que des valeurs qui existent dans une autre table. Voici REFERENCES, ON DELETE CASCADE, SET NULL et RESTRICT, l'ajout d'une clé à une table existante et son index.
- Types enumUn type enum est une liste fixe et ordonnée de valeurs autorisées, créée avec CREATE TYPE ... AS ENUM. Ajouter et renommer des valeurs, les lister, les trier, et quand une contrainte CHECK est un meilleur choix.
- Timestampstimestamptz stocke un instant précis et l'affiche dans le fuseau horaire de la session ; timestamp stocke une heure d'horloge sans fuseau. Aucun des deux ne garde le fuseau saisi. Utilisez timestamptz pour presque tout.
Requêtes
- JointuresUne jointure combine les lignes de deux tables selon une condition. INNER JOIN garde les correspondances, LEFT JOIN garde toutes les lignes de la première table, FULL JOIN celles des deux, CROSS JOIN associe tout, et LATERAL lance une sous-requête par ligne.
- DISTINCT ONSELECT DISTINCT ON (clé) ... ORDER BY clé, tri garde la première ligne de chaque groupe, comme la dernière commande de chaque client. C'est propre à PostgreSQL.
- COALESCE, NULLIF et CASECOALESCE(a, b, ...) renvoie le premier argument qui n'est pas NULL, NULLIF(a, b) transforme une valeur en NULL, et CASE WHEN choisit une valeur selon une condition.
- LIKE, ILIKE et regexLIKE compare du texte à un motif avec % et _, ILIKE fait la même chose sans tenir compte de la casse, et les opérateurs ~ testent des expressions régulières POSIX.
- CAST et ::CAST(valeur AS type) et valeur::type convertissent une valeur vers un autre type dans PostgreSQL. C'est la même conversion ; :: est la syntaxe courte de PostgreSQL.
Modifier les données
- Upsert (ON CONFLICT)L'upsert dans PostgreSQL avec INSERT ... ON CONFLICT : DO UPDATE avec EXCLUDED, DO NOTHING, mises à jour conditionnelles, compteurs, et comment distinguer les lignes insérées des lignes mises à jour.
- RETURNINGRETURNING fait renvoyer à INSERT, UPDATE, DELETE et MERGE les lignes qu'ils ont modifiées : id générés, nouvelles valeurs, lignes supprimées et action exécutée par MERGE.
- TransactionsLe fonctionnement des transactions PostgreSQL : BEGIN, COMMIT et ROLLBACK, les savepoints, l'effet d'une erreur sur une transaction ouverte et les niveaux d'isolation.
JSON et tableaux
- JSONBStockez et interrogez du JSON dans PostgreSQL avec jsonb : les opérateurs -> et ->>, la contenance avec @>, jsonb_set et les autres mises à jour, les tableaux transformés en lignes, les index GIN, et json ou jsonb.
- TableauxLes colonnes de type tableau dans PostgreSQL : les déclarer, l'indexation à partir de 1, vérifier si un tableau contient une valeur avec ANY, @> et &&, unnest, et les fonctions de tableau utiles.
- string_agg et array_aggstring_agg concatène les valeurs d'un groupe en une seule chaîne et array_agg les rassemble dans un tableau. Tri, DISTINCT, FILTER, gestion de NULL et les erreurs fréquentes.
Chaînes et dates
- Fonctions de chaînesLes fonctions de chaînes PostgreSQL du quotidien : substring, left et right, || et concat, split_part, position, replace, regexp_replace, trim, lpad et format.
- Fonctions de dateTravailler avec les dates dans PostgreSQL : now() et current_date, ajouter et soustraire des intervalles, date_trunc pour regrouper, extract et age, et la mise en forme avec to_char.
- generate_seriesgenerate_series renvoie un ensemble de lignes d'une valeur de départ à une valeur d'arrivée : nombres, dates et timestamps. Utilisez-la pour combler les trous d'un rapport, générer des données de test et construire des calendriers.
Requêtes avancées
- CTE (WITH)Une expression de table commune nomme une sous-requête dans une clause WITH, que la requête principale lit comme une table. WITH RECURSIVE lui permet de se référer à elle-même pour parcourir arbres et graphes.
- Window functionsLes window functions calculent une valeur pour chaque ligne à partir d'un groupe de lignes liées, sans les fusionner : classements, totaux cumulés, moyennes mobiles et comparaisons avec la ligne précédente.
Performances et maintenance
- IndexCREATE INDEX construit par défaut un index B-tree pour que PostgreSQL trouve les lignes sans lire toute la table. Les autres types (GIN, GiST, BRIN, hash) couvrent JSONB, les tableaux, la recherche plein texte, les ranges et les très grosses tables.
- EXPLAIN ANALYZEEXPLAIN affiche le plan que PostgreSQL choisit pour une requête, et EXPLAIN ANALYZE exécute la requête et ajoute le nombre réel de lignes et le temps de chaque étape. Apprenez à lire l'arbre du plan et les noms de nœuds courants.
- Vues et vues matérialiséesUne vue est une requête enregistrée qui s'exécute à chaque lecture. Une vue matérialisée stocke le résultat de la requête et ne change que lorsque vous lancez REFRESH MATERIALIZED VIEW.
- VACUUMVACUUM supprime les versions mortes des lignes que laissent UPDATE et DELETE, pour que leur espace soit réutilisé. L'autovacuum le lance pour vous ; cette page montre quand, comment le régler, et quand VACUUM FULL vaut son verrou.
Fonctions et triggers
- Fonctions et procéduresCREATE FUNCTION définit une fonction que vous appelez dans vos requêtes, écrite en SQL ou en PL/pgSQL. CREATE PROCEDURE définit une routine que vous lancez avec CALL et qui peut valider des transactions en cours de route.
- TriggersUn trigger exécute une fonction automatiquement quand des lignes sont insérées, mises à jour ou supprimées. Dans PostgreSQL, vous écrivez une fonction trigger qui renvoie trigger, puis vous l'attachez à une table avec CREATE TRIGGER.