INSTITUT DE FORMATION TECHNIQUE TOAMASINA
Année universitaire 2024-2025
Administration PostgreSQL
INFO 3
Jimmy Francice MENAVOARA
1
Introduction
❑Objectif général du cours
Ce cours vise à initier les étudiants à PostgreSQL, un système de gestion de base
de données relationnelle, en partant des concepts fondamentaux jusqu'à la mise
en œuvre de requêtes SQL, la création de bases de données et leur manipulation
à l'aide d'outils et de langages comme PHP ou Python.
2
Introduction
❑Définitions
❖ Base de données
Une base de données est un ensemble structuré d'informations stockées de
manière organisée pour être facilement consultées, modifiées et gérées par des
logiciels.
❖ PostgreSQL
PostgreSQL est un Système de Gestion de Base de Données Relationnelle
(SGBDR) orienté objet, libre et open-source.
3
Introduction
❑Caractéristiques clés de PostgreSQL ❑Avantages de PostgreSQL
✓ Transactions ACID ✓ Libre et gratuit
✓ Types de données riches (JSON, tableaux, ✓ Utilisé par de grandes entreprises (Airbnb,
etc.) Spotify...)
✓ Extensible ✓ Intégration facile avec PHP, Python, etc.
✓ Sécurité intégrée ✓ Multi-plateforme
✓ Support spatial (Post GIS)
✓ Multi-utilisateurs
4
Installation
❑Installation de PostgreSQL sous Linux (Ubuntu/Debian)
❖ Mise à jour du système
sudo apt update
sudo apt upgrade
❖ Installation de PostgreSQL :
sudo apt install postgresql postgresql-contrib
❖ Vérification du statut du service PostgreSQL :
sudo systemctl status postgresql
Pour démarrer le service : sudo systemctl start postgresql
Pour l'activer au démarrage : sudo systemctl enable postgresql
5
Installation
❑Installation de PostgreSQL sous Linux (Ubuntu/Debian)
❖ Connexion à PostgreSQL en tant que super utilisateur :
sudo -i -u postgres
psql
Pour quitter psql : \q
❖ Création d'un utilisateur et d'une base :
CREATE USER alice WITH PASSWORD 'motdepasse’;
CREATE DATABASE alicedb;
GRANT ALL PRIVILEGES ON DATABASE alicedb TO alice;
❖ Accès avec PgAdmin
sudo apt install pgadmin4
6
Installation
❑Installation de PostgreSQL sous Linux (Ubuntu/Debian)
❖ Tester la connexion avec l'utilisateur créé :
psql -U alice -d alicedb -h localhost
❑Installation sur Fedora / RedHat / CentOs
✓ sudo dnf install postgresql-server postgresql-contrib
✓ sudo postgresql-setup –initdb
✓ sudo systemctl start postgresql
✓ sudo systemctl enable postgresql
7
Accéder à une base de données
Une fois que vous avez créé la base, vous pouvez y accéder :
✓ Démarrez le programme en ligne de commande de PostgreSQL, appelé psql, qui vous permet de saisir,
d'éditer et d'exécuter de manière interactive des commandes SQL.
✓ Utilisez un outil existant avec une interface graphique comme pgAdmin ou une suite bureautique avec
un support ODBC ou JDBC pour créer et manipuler une base.
✓ Écrivez une application personnalisée en utilisant un des nombreux langages disponibles.
Exemple:
8
Accéder à une base de données
✓ Dans psql, vous serez accueilli avec le message suivant :
✓ La dernière ligne peut aussi être :
Cela veut dire que vous êtes le super utilisateur de la base de
données, ce qui est souvent le cas si vous avez
installé PostgreSQL vous-même.
9
Accéder à une base de données
✓ Quelques exemples de commande
Le programme psql dispose d'un certain nombre de commandes internes
qui ne sont pas des commandes SQL. 10
Le langage SQL
✓ Concepts
PostgreSQL est un système de gestion de bases de données relationnelles (SGBDR). Cela signifie que c'est un
système pour gérer des données stockées dans des relations. Relation est essentiellement un terme
mathématique pour table. La notion de stockage de données dans des tables est si commune aujourd'hui
que cela peut sembler en soi évident, mais il y a de nombreuses autres manières d'organiser des bases
de données.
✓ Créer une nouvelle Table
11
Le langage SQL
Le second exemple stockera des villes et leur emplacement géographique associé :
Le type point est un exemple d'un type de données spécifique à PostgreSQL.
Pour finir, vous devez savoir que si vous n'avez plus besoin d'une table ou que vous
voulez la recréer différemment, vous pouvez la supprimer en utilisant la commande
suivante :
12
Le langage SQL
✓ Remplir une table avec des lignes
L'instruction INSERT est utilisée pour remplir une table avec des lignes :
Notez que tous les types utilisent des formats d'entrées plutôt évidents. Les constantes qui ne
sont pas des valeurs numériques simples doivent être habituellement entourées par des
guillemets simples (') comme dans l'exemple.
13
Le langage SQL
✓ Remplir une table avec des lignes
La syntaxe utilisée jusqu'à maintenant nécessite de se rappeler l'ordre des colonnes.
Une syntaxe alternative vous autorise à lister les colonnes explicitement :
Vous pouvez lister les colonnes dans un ordre différent si vous le souhaitez ou même
omettre certaines colonnes ; par exemple, si la précipitation est inconnue :
De nombreux développeurs considèrent que le listage explicite des colonnes
est un meilleur style que de compter sur l'ordre implicite. 14
Le langage SQL
✓ Interroger une table
Pour retrouver les données d'une table, elle est interrogée. Une
instruction SQL SELECT est utilisée pour faire cela. L'instruction est divisée en liste de
sélection (la partie qui liste les colonnes à retourner), une liste de tables (la partie qui
liste les tables à partir desquelles les données seront retrouvées) et une qualification
optionnelle (la partie qui spécifie les restrictions). Par exemple, pour retrouver toutes
les lignes de la table temps, saisissez :
Ici, * est un raccourci pour « toutes les colonnes ». Donc, le même résultat
pourrait être obtenu avec :
15
Le langage SQL
✓ Interroger une table
Le résultat devrait être ceci :
Vous pouvez écrire des expressions, pas seulement des références à
de simples colonnes, dans la liste de sélection. Par exemple, vous
pouvez faire :
16
Le langage SQL
✓ Interroger une table
Cela devrait donner :
Notez comment la clause AS est utilisée pour renommer la sortie d'une colonne (cette clause AS est
optionnelle).
Une requête peut être « qualifiée » en ajoutant une clause WHERE qui spécifie les lignes souhaitées. La
clause WHERE contient une expression booléenne et seules les lignes pour lesquelles l'expression
booléenne est vraie sont renvoyées. Les opérateurs booléens habituels (AND, OR et NOT) sont autorisés
dans la qualification. Par exemple, ce qui suit recherche le temps à San Francisco les jours pluvieux :
17
Le langage SQL
✓ Interroger une table
Résultat :
Vous pouvez demander à ce que les résultats d'une requête soient renvoyés dans un ordre
trié :
18
Le langage SQL
✓ Interroger une table
Dans cet exemple, l'ordre de tri n'est pas spécifié complètement, donc vous pouvez obtenir les lignes
San Francisco dans n'importe quel ordre. Mais, vous auriez toujours obtenu les résultats affichés ci-
dessus si vous aviez fait :
19
Le langage SQL
✓ Interroger une table
Vous pouvez demander que les lignes dupliquées soient supprimées du résultat d'une requête :
De nouveau, l'ordre des lignes résultat pourrait varier. Vous pouvez vous assurer des résultats cohérents en
utilisant DISTINCT et ORDER BY ensemble :
20
Le langage SQL
✓ Jointures entre les tables
Jusqu'ici, nos requêtes avaient seulement consulté une table à la fois. Les requêtes peuvent accéder à plusieurs
tables en même temps ou accéder à la même table de façon à ce que plusieurs lignes de la table soient traitées en
même temps. Les requêtes qui accèdent à plusieurs tables (ou plusieurs fois la même table) sont appelées des
requêtes de jointure. Elles combinent les lignes d'une table avec les lignes d'une deuxième table, avec une
expression indiquant comment réaliser la jointure entre ces lignes. Par exemple, pour renvoyer toutes les lignes de
la table temps avec l'emplacement de la ville associée, la base de données doit comparer la colonne ville de
chaque ligne de la table temps avec la colonne nom de toutes les lignes de la table villes et que vous choisissiez
les paires de lignes où ces valeurs correspondent. Ceci sera accompli avec la requête suivante :
21
Le langage SQL
✓ Jointures entre les tables
Deux remarques à propos du résultat :
• Il n'y a pas de lignes pour la ville de Hayward dans le résultat. C'est parce qu'il n'y a aucune entrée
correspondante dans la table villes pour Hayward, donc la jointure ignore les lignes n'ayant pas de
correspondance avec la table temps. Nous verrons rapidement comment cela peut être résolu.
• Il y a deux colonnes contenant le nom des villes. C'est correct, car les listes des colonnes des
tables temps et villes sont concaténées. En pratique, ceci est indésirable, vous voudrez probablement lister les
colonnes explicitement plutôt que d'utiliser * :
22
Le langage SQL
✓ Jointures entre les tables
Puisque toutes les colonnes ont un nom différent, l'analyseur a automatiquement trouvé à quelle
table elles appartiennent. Si des noms de colonnes sont communs entre les deux tables, vous
aurez besoin de qualifier les noms des colonnes pour préciser celles dont vous parlez. Par
exemple :
23
Le langage SQL
✓ Jointures entre les tables
La qualification des noms de colonnes dans une requête de jointure est fréquemment considérée comme une
bonne pratique. Cela évite l'échec de la requête si un nom de colonne dupliqué est ajouté plus tard dans une des
tables.
Les requêtes de jointure vues jusqu'ici peuvent aussi être écrites sous cette forme :
Cette syntaxe date d'avant la syntaxe JOIN/ON, qui a été introduite dans SQL-92. Les tables sont simplement listées
dans la clause FROM, et l'expression de comparaison est ajoutée dans la clause WHERE. Les résultats de l'ancienne
syntaxe implicite et de la nouvelle syntaxe explicite JOIN/ON sont identiques. Cependant, pour celui qui lit la requête,
la syntaxe explicite est plus facile à comprendre : la condition de jointure est introduite par son propre mot clé alors
qu'auparavant, la condition était mélangée dans la clause WHERE avec les autres conditions.
24
Le langage SQL
✓ Jointures entre les tables
Maintenant, nous allons essayer de comprendre comment nous pouvons avoir les entrées de Hayward. Nous
voulons que la requête parcoure la table temps et que, pour chaque ligne, elle trouve la (ou les) ligne(s)
de villes correspondante(s). Si aucune ligne correspondante n'est trouvée, nous voulons que les valeurs des
colonnes de la table villes soient remplacées par des « valeurs vides ». Ce genre de requêtes est appelé jointure
externe (outer join). (Les jointures que nous avons vues jusqu'ici sont des jointures internes -- inner joins). La
commande ressemble à cela :
25
Le langage SQL
✓ Jointures entre les tables
Cette requête est appelée une jointure externe à gauche (left outer join) parce que la table mentionnée à la gauche
de l'opérateur de jointure aura au moins une fois ses lignes dans le résultat, tandis que la table sur la droite aura
seulement les lignes qui correspondent à des lignes de la table de gauche. Lors de l'affichage d'une ligne de la
table de gauche pour laquelle il n'y a pas de correspondance dans la table de droite, des valeurs vides (appelées
NULL) sont utilisées pour les colonnes de la table de droite.
26
Le langage SQL
✓ Jointures entre les tables
Exercice : Il existe aussi des jointures externes à droite et des jointures externes complètes. Essayez de trouver ce
qu'elles font.
Nous pouvons également joindre une table avec elle-même. Ceci est appelé une jointure réflexive. Comme
exemple, supposons que nous voulions trouver toutes les entrées de temps qui sont dans un intervalle de
températures d'autres entrées de temps. Nous avons donc besoin de comparer les colonnes t_basse et t_haute de
chaque ligne de temps aux colonnes t_basse et t_haute de toutes les autres lignes de temps. Nous pouvons faire
cela avec la requête suivante :
27
Le langage SQL
✓ Jointures entre les tables
28
Le langage SQL
✓ Jointures entre les tables
Dans cet exemple, nous avons renommé la table temps en T1 et en T2 pour être capables de distinguer
respectivement le côté gauche et le côté droit de la jointure. Vous pouvez aussi utiliser ce genre d'alias dans d'autres
requêtes pour économiser de la frappe, c'est-à-dire :
29
Le langage SQL
✓ Fonctions d’agrégat
Comme la plupart des autres produits de bases de données relationnelles, PostgreSQL supporte
les fonctions d'agrégat. Une fonction d'agrégat calcule un seul résultat à partir de plusieurs lignes en entrée.
Par exemple, il y a des agrégats pour calculer le nombre (count), la somme (sum), la moyenne (avg), le
maximum (max) et le minimum (min) d'un ensemble de lignes.
Comme exemple, nous pouvons trouver la température la plus haute parmi les températures basses avec :
30
Le langage SQL
✓ Fonctions d’agrégat
Si nous voulons connaître dans quelle ville (ou villes) ces lectures se sont produites, nous pouvons essayer :
mais cela ne marchera pas puisque l'agrégat max ne peut pas être utilisé dans une clause WHERE (cette restriction
existe parce que la clause WHERE détermine les lignes qui seront traitées par l'agrégat ; donc les lignes doivent être
évaluées avant que les fonctions d'agrégat ne calculent leur résultat). Cependant, comme cela est souvent le cas,
la requête peut être répétée pour arriver au résultat attendu, ici en utilisant une sous-requête :
31
Le langage SQL
✓ Fonctions d’agrégat
Ceci est correct, car la sous-requête est un calcul indépendant qui traite son propre agrégat séparément à partir de
ce qui se passe dans la requête externe.
Les agrégats sont également très utiles s'ils sont combinés avec les clauses GROUP BY. Par exemple, nous pouvons
obtenir le nombre de prises de température et la température la plus haute parmi les températures basses
observées dans chaque ville avec :
32
Le langage SQL
✓ Fonctions d’agrégat
ce qui nous donne une ligne par ville dans le résultat. Chaque résultat d'agrégat est calculé avec les lignes de la
table correspondant à la ville. Nous pouvons filtrer ces lignes groupées en utilisant HAVING :
33
Le langage SQL
✓ Fonctions d’agrégat
ce qui nous donne le même résultat uniquement pour les villes qui ont toutes leurs valeurs de t_basse en dessous
de 40. Pour finir, si nous nous préoccupons seulement des villes dont le nom commence par « S », nous pouvons
faire :
34
Le langage SQL
✓ Fonctions d’agrégat
Il est important de comprendre l'interaction entre les agrégats et les clauses SQL WHERE et HAVING. La différence
fondamentale entre WHERE et HAVING est que WHERE sélectionne les lignes en entrée avant que les groupes et les
agrégats ne soient traités (donc, cette clause contrôle les lignes qui se retrouvent dans le calcul de l'agrégat), tandis
que HAVING sélectionne les lignes groupées après que les groupes et les agrégats ont été traités. Donc, la
clause WHERE ne doit pas contenir de fonctions d'agrégat ; cela n'a aucun sens d'essayer d'utiliser un agrégat pour
déterminer les lignes en entrée des agrégats. D'un autre côté, la clause HAVING contient toujours des fonctions
d'agrégat (pour être précis, vous êtes autorisés à écrire une clause HAVING qui n'utilise pas d'agrégat, mais c'est
rarement utilisé. La même condition pourra être utilisée plus efficacement par un WHERE).
35
Le langage SQL
✓ Fonctions d’agrégat
Dans l'exemple précédent, nous pouvons appliquer la restriction sur le nom de la ville dans la
clause WHERE puisque cela ne nécessite aucun agrégat. C'est plus efficace que d'ajouter la restriction
dans HAVING parce que nous évitons le groupement et les calculs d'agrégat pour toutes les lignes qui ont échoué
lors du contrôle fait par WHERE.
Une autre façon de sélectionner les lignes qui vont dans le calcul d'un agrégat est d'utiliser la clause FILTER, qui est
une option par agrégat :
36
Le langage SQL
✓ Fonctions d’agrégat
FILTER ressemble beaucoup à WHERE, sauf qu'elle supprime les lignes uniquement sur l'entrée de la fonction
d'agrégat à laquelle elle est attachée. Dans cet exemple, l'agrégat count compte seulement les lignes pour
lesquelles la colonne t_basse a une valeur inférieure à 45 alors que l'agrégat max est toujours appliqué à toutes les
lignes, donc il trouve toujours la valeur 46.
37
Le langage SQL
✓ Mise à jour
Vous pouvez mettre à jour une ligne existante en utilisant la commande UPDATE. Supposez que vous découvrez que
les températures sont toutes excédentes de deux degrés après le 28 novembre. Vous pouvez corriger les données
de la façon suivante :
Regardez le nouvel état des données :
38
Le langage SQL
✓ Suppression
Les lignes peuvent être supprimées de la table avec la commande DELETE. Supposez que vous ne soyez plus
intéressé par le temps de Hayward. Vous pouvez faire ce qui suit pour supprimer ses lignes de la table :
Toutes les entrées de temps pour Hayward sont supprimées.
39