Gestion des bases de données et des tables

Bonne lecture et bon apprentissage !
Junior TSAFACK – 20/08/2026
⏱️ Temps de lecture estimé : 10 minutes

---

Après avoir appris à vous connecter avec psql, il est temps de passer à la création et la gestion des bases de données et des tables. Ce cours vous présente toutes les options disponibles pour créer des bases de données (encodage, templates, propriétaires) et des tables (types de colonnes, contraintes, héritage, partitionnement).

---

Rappel : Hiérarchie PostgreSQL

Avant d’entrer dans le vif, rappelons la hiérarchie des objets :

Machine (serveur)
  └── Cluster PostgreSQL (instance)
        └── Base de données (Database)
              └── Schéma (Schema) — par défaut : public
                    └── Table
                          └── Colonnes (champs) et Lignes (enregistrements)

---

Gestion avancée des bases de données

Syntaxe complète de CREATE DATABASE

La commande CREATE DATABASE accepte de nombreuses options :

CREATE DATABASE name
    [ [ WITH ]
        [ OWNER [=] user_name ]
        [ TEMPLATE [=] template ]
        [ ENCODING [=] encoding ]
        [ LC_COLLATE [=] lc_collate ]
        [ LC_CTYPE [=] lc_ctype ]
        [ TABLESPACE [=] tablespace ]
        [ CONNECTION LIMIT [=] connlimit ]
        [ IS_TEMPLATE [=] istemplate ]
    ]

Options principales

Option	Description	Exemple
OWNER	Propriétaire de la base (par défaut, l’utilisateur qui exécute la commande).	OWNER = junior
TEMPLATE	Base modèle utilisée pour la copie. template0 est la base vierge, template1 est la base par défaut.	TEMPLATE = template0
ENCODING	Encodage des caractères (UTF8, LATIN1, etc.).	ENCODING = 'UTF-8'
LC_COLLATE	Règle de tri des chaînes de caractères.	LC_COLLATE = 'fr_FR.UTF-8'
LC_CTYPE	Classification des caractères (majuscules, minuscules).	LC_CTYPE = 'fr_FR.UTF-8'
TABLESPACE	Tablespace où stocker la base.	TABLESPACE = fast_ssd
CONNECTION LIMIT	Nombre maximum de connexions simultanées.	CONNECTION LIMIT = 100
IS_TEMPLATE	Si true, la base peut être utilisée comme modèle.	IS_TEMPLATE = true

Exemple de création avancée

CREATE DATABASE magasin
    WITH
        OWNER = junior
        ENCODING = 'UTF-8'
        LC_COLLATE = 'fr_FR.UTF-8'
        LC_CTYPE = 'fr_FR.UTF-8'
        TEMPLATE = template0
        CONNECTION LIMIT = 50;

Templates : template0 vs template1

PostgreSQL dispose de deux bases modèles :

• template1 : Base modèle par défaut. Vous pouvez y ajouter des objets (extensions, fonctions) qui seront copiés dans toute nouvelle base.
• template0 : Base vierge, strictement identique à l’état initial de template1. Utilisez-la si vous voulez une base pure, sans aucun objet ajouté.

Bonnes pratiques :

• Pour créer une base avec un encodage différent de celui de template1, utilisez TEMPLATE template0.
• Ne supprimez jamais template0 ou template1 (ou alors avec précaution).

-- Créer une base avec un encodage spécifique
CREATE DATABASE magasin_utf8
    TEMPLATE template0
    ENCODING 'UTF-8';

💡 Bon à savoir : La fonction pg_encoding_to_char() permet de connaître l’encodage d’une base :

SELECT datname, pg_encoding_to_char(encoding) FROM pg_database;

---

Les types de données courants

PostgreSQL propose une riche palette de types de données. Voici les plus utilisés :

Type	Description	Exemple
INTEGER (ou INT)	Nombre entier sur 4 octets (-2⁶³ à 2⁶³-1)	id INTEGER
BIGINT	Nombre entier sur 8 octets	population BIGINT
SERIAL	Entier auto-incrémenté (équivalent à INTEGER avec séquence)	id SERIAL PRIMARY KEY
BIGSERIAL	Auto-incrémenté sur 8 octets	id BIGSERIAL
NUMERIC(p,s)	Nombre décimal précis (p = total chiffres, s = décimales)	prix NUMERIC(10,2)
VARCHAR(n)	Chaîne de caractères de longueur variable (max n)	nom VARCHAR(50)
TEXT	Chaîne de caractères sans limite de taille	description TEXT
DATE	Date (jour, mois, année)	date_naissance DATE
TIMESTAMP	Date et heure	created_at TIMESTAMP
BOOLEAN	Vrai ou faux	actif BOOLEAN
JSON / JSONB	Données JSON (binaire pour JSONB)	preferences JSONB

Exemple de création de table avec différents types

CREATE TABLE commandes (
    id SERIAL PRIMARY KEY,
    client_id INTEGER NOT NULL,
    montant NUMERIC(10,2) NOT NULL,
    date_commande TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    est_livree BOOLEAN DEFAULT FALSE,
    notes TEXT,
    adresse JSONB
);

---

Gestion des tables

Création d’une table avec contraintes

Les contraintes garantissent l’intégrité des données. PostgreSQL supporte toutes les contraintes du standard SQL :

CREATE TABLE clients (
    id SERIAL PRIMARY KEY,                          -- Clé primaire auto-incrémentée
    nom VARCHAR(50) NOT NULL,                       -- Ne peut pas être NULL
    email VARCHAR(100) UNIQUE,                      -- Doit être unique
    telephone VARCHAR(20) CHECK (telephone ~ '^[0-9]{10}$'),  -- Vérification de format
    date_inscription DATE DEFAULT CURRENT_DATE,     -- Valeur par défaut
    categorie VARCHAR(20) DEFAULT 'standard'        -- Valeur par défaut
);

-- Clé étrangère vers une autre table
CREATE TABLE commandes (
    id SERIAL PRIMARY KEY,
    client_id INTEGER NOT NULL,
    montant NUMERIC(10,2) NOT NULL,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
);

Types de contraintes

Contrainte	Description	Exemple
PRIMARY KEY	Identifiant unique et non null.	id SERIAL PRIMARY KEY
FOREIGN KEY	Référence vers une clé primaire d’une autre table.	client_id INTEGER REFERENCES clients(id)
UNIQUE	Empêche les valeurs dupliquées.	email VARCHAR(100) UNIQUE
NOT NULL	Empêche les valeurs NULL.	nom VARCHAR(50) NOT NULL
CHECK	Valide une condition sur la colonne.	age INTEGER CHECK (age >= 18)
DEFAULT	Valeur par défaut.	date_commande TIMESTAMP DEFAULT CURRENT_TIMESTAMP

---

Tables particulières

Tables temporaires (TEMP)

Les tables temporaires existent uniquement pour la durée de la session (ou de la transaction). Elles sont automatiquement supprimées à la déconnexion.

CREATE TEMPORARY TABLE temp_panier (
    produit_id INTEGER,
    quantite INTEGER,
    prix_unitaire NUMERIC(10,2)
);

-- Utilisation comme une table normale
INSERT INTO temp_panier VALUES (1, 2, 29.99);
SELECT * FROM temp_panier;

Cas d’usage : Stocker des résultats intermédiaires pour une session utilisateur, sans persister les données.

Tables non loguées (UNLOGGED)

Les tables UNLOGGED n’écrivent pas dans les journaux de transactions (WAL). Elles sont donc plus rapides en écriture, mais perdent leurs données en cas de crash.

CREATE UNLOGGED TABLE logs_acces (
    id SERIAL PRIMARY KEY,
    utilisateur VARCHAR(50),
    date_acces TIMESTAMP,
    action TEXT
);

Cas d’usage : Données temporaires, logs, cache, ou données facilement recréables.

Comparaison des performances :

-- Table normale
CREATE TABLE test_normal (id INT);
INSERT INTO test_normal SELECT * FROM generate_series(1, 1000000);
-- Temps : ~2.5 secondes

-- Table UNLOGGED
CREATE UNLOGGED TABLE test_unlogged (id INT);
INSERT INTO test_unlogged SELECT * FROM generate_series(1, 1000000);
-- Temps : ~1.2 secondes (environ 2x plus rapide)

---

Héritage de tables (table inheritance)

L’héritage permet à une table enfant de récupérer toutes les colonnes de la table parente.

-- Table parente
CREATE TABLE vehicules (
    id SERIAL PRIMARY KEY,
    marque VARCHAR(50),
    modele VARCHAR(50),
    prix NUMERIC(10,2)
);

-- Table enfant qui hérite de vehicules
CREATE TABLE voitures (
    nb_portes INTEGER
) INHERITS (vehicules);

-- Table enfant avec colonne supplémentaire
CREATE TABLE motos (
    cylindree INTEGER
) INHERITS (vehicules);

Test :

INSERT INTO vehicules (marque, modele, prix) VALUES ('Toyota', 'Camry', 35000);
INSERT INTO voitures (marque, modele, prix, nb_portes) VALUES ('Peugeot', '208', 18000, 5);
INSERT INTO motos (marque, modele, prix, cylindree) VALUES ('Yamaha', 'MT-07', 8000, 689);

-- La table parente contient toutes les lignes
SELECT * FROM vehicules;
-- Retourne 3 lignes (Toyota, Peugeot, Yamaha)

-- Seulement les voitures
SELECT * FROM voitures;
-- Retourne 1 ligne (Peugeot)

💡 Bon à savoir : L’héritage de tables est une fonctionnalité avancée. PostgreSQL propose désormais le partitionnement comme alternative plus performante et standard.

---

Partitionnement de tables

Le partitionnement divise une table en plusieurs partitions (sous-tables) en fonction d’une colonne (par exemple, une date). Cela améliore les performances des requêtes et facilite la maintenance.

Partitionnement par plage (RANGE)

Créer la table partitionnée :

CREATE TABLE temperature (
    mesure_timestamp TIMESTAMPTZ NOT NULL,
    sensor_id INTEGER NOT NULL,
    valeur NUMERIC(5,2) NOT NULL
) PARTITION BY RANGE (mesure_timestamp);

Créer les partitions :

CREATE TABLE temperature_2024_09 PARTITION OF temperature
    FOR VALUES FROM ('2024-09-01') TO ('2024-10-01');

CREATE TABLE temperature_2024_10 PARTITION OF temperature
    FOR VALUES FROM ('2024-10-01') TO ('2024-11-01');

CREATE TABLE temperature_2024_11 PARTITION OF temperature
    FOR VALUES FROM ('2024-11-01') TO ('2024-12-01');

Insérer des données :

INSERT INTO temperature (mesure_timestamp, sensor_id, valeur)
SELECT
    '2024-10-15'::timestamptz + (interval '1 minute' * generate_series(0, 1000)),
    1,
    (25.0 + random() * 10)::NUMERIC(5,2);

PostgreSQL dirige automatiquement les lignes vers la bonne partition en fonction de la date.

Partitionnement par liste (LIST)

CREATE TABLE produits (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(100),
    categorie VARCHAR(20)
) PARTITION BY LIST (categorie);

CREATE TABLE produits_electronique PARTITION OF produits
    FOR VALUES IN ('informatique', 'audio', 'video');

CREATE TABLE produits_maison PARTITION OF produits
    FOR VALUES IN ('electromenager', 'decoration');

---

Gestion des tables : ALTER, DROP, TRUNCATE

Modifier une table (ALTER TABLE)

-- Ajouter une colonne
ALTER TABLE clients ADD COLUMN date_naissance DATE;

-- Modifier le type d’une colonne
ALTER TABLE clients ALTER COLUMN telephone TYPE VARCHAR(15);

-- Ajouter une contrainte
ALTER TABLE clients ADD CONSTRAINT age_min CHECK (age >= 18);

-- Renommer une colonne
ALTER TABLE clients RENAME COLUMN nom TO nom_complet;

-- Supprimer une colonne
ALTER TABLE clients DROP COLUMN ancienne_colonne;

-- Renommer la table
ALTER TABLE clients RENAME TO clients_v2;

Supprimer une table (DROP TABLE)

DROP TABLE ancienne_table;

-- Supprimer avec dépendances (CASCADE)
DROP TABLE clients CASCADE;

CASCADE supprime également les objets qui dépendent de la table (vues, clés étrangères).

Vider une table (TRUNCATE)

TRUNCATE TABLE logs;

-- Vider plusieurs tables
TRUNCATE TABLE logs, temp, cache;

-- Vider avec cascade (truncate les tables liées par clé étrangère)
TRUNCATE TABLE clients CASCADE;

Différence avec DELETE :

• TRUNCATE est plus rapide car il ne génère pas de logs de lignes.
• TRUNCATE réinitialise les séquences (auto-incrémentation).
• TRUNCATE ne peut pas être annulé par un ROLLBACK si utilisé avec AUTOCOMMIT.

---

Exemple complet de session

Voici un exemple complet qui illustre la création d’une base, d’un schéma, de tables avec contraintes, héritage et partitionnement :

-- 1. Créer une base
CREATE DATABASE entreprise
    WITH ENCODING 'UTF-8'
    OWNER = postgres;

-- 2. Se connecter
\c entreprise

-- 3. Créer un schéma
CREATE SCHEMA rh;

-- 4. Créer une table dans le schéma
CREATE TABLE rh.employes (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(50) NOT NULL,
    prenom VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    date_embauche DATE DEFAULT CURRENT_DATE,
    salaire NUMERIC(10,2) CHECK (salaire > 0)
);

-- 5. Table avec héritage (gestion des congés)
CREATE TABLE rh.conges (
    id SERIAL PRIMARY KEY,
    employe_id INTEGER REFERENCES rh.employes(id),
    date_debut DATE NOT NULL,
    date_fin DATE NOT NULL,
    type VARCHAR(20) DEFAULT 'payes'
);

-- 6. Partitionnement des congés par année
CREATE TABLE rh.conges_partitionne (
    id SERIAL,
    employe_id INTEGER,
    date_debut DATE NOT NULL,
    date_fin DATE NOT NULL,
    type VARCHAR(20)
) PARTITION BY RANGE (date_debut);

CREATE TABLE rh.conges_2024 PARTITION OF rh.conges_partitionne
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE rh.conges_2025 PARTITION OF rh.conges_partitionne
    FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- 7. Insérer des données
INSERT INTO rh.employes (nom, prenom, email, salaire) VALUES
    ('Dupont', 'Jean', '[email protected]', 35000),
    ('Martin', 'Sophie', '[email protected]', 42000);

INSERT INTO rh.conges_partitionne (employe_id, date_debut, date_fin) VALUES
    (1, '2024-07-15', '2024-07-22'),
    (2, '2024-12-20', '2025-01-05');

-- 8. Interroger les partitions
SELECT * FROM rh.conges_2024;  -- 2 lignes
SELECT * FROM rh.conges_2025;  -- 1 ligne (le congé qui chevauche 2025)

---

Prochain chapitre

Vous savez maintenant créer et gérer bases et tables. Dans le prochain cours, vous découvrirez les index, qui sont essentiels pour optimiser les performances des requêtes.

👉 Cours 5 : Les index – B-tree et Hash

---

Junior TSAFACK – 20/08/2026