PostgreSQL : types, JSONB et écritures avancées

PostgreSQL est le SGBD relationnel open source le plus utilisé pour les nouvelles applications web et les plateformes data. Il respecte bien le standard SQL, offre des types riches et un optimiseur performant. Ce cours se base sur PostgreSQL 18 (sorti fin 2025) ; tout ce qui est présenté fonctionne aussi sur les versions récentes antérieures, sauf mention contraire.

Des types précis

CREATE TABLE commandes (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    reference   uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
    client_id   bigint NOT NULL REFERENCES clients(id),
    montant     numeric(10,2) NOT NULL CHECK (montant >= 0),
    statut      text NOT NULL DEFAULT 'en_attente',
    options     jsonb NOT NULL DEFAULT '{}',
    cree_le     timestamptz NOT NULL DEFAULT now()
);

• GENERATED ALWAYS AS IDENTITY est la forme standard d’auto-incrément ; serial reste accepté mais n’est plus recommandé.
• uuid donne des identifiants non devinables, utiles dans les URL d’une API. gen_random_uuid() produit des UUID v4 ; PostgreSQL 18 ajoute uuidv7(), dont les valeurs croissent avec le temps et s’indexent mieux.
• numeric est exact : indispensable pour l’argent.
• text n’a pas de pénalité de performance par rapport à varchar(n) ; on ajoute un CHECK si une longueur maximale est une vraie règle métier.
• timestamptz enregistre un instant (converti en UTC) et l’affiche dans le fuseau de la session (SET TIME ZONE 'Europe/Paris'). Le type timestamp sans fuseau est ambigu dès que plusieurs fuseaux sont en jeu.

JSONB : de la souplesse dans un modèle relationnel

jsonb stocke du JSON sous une forme binaire, indexable. Il est idéal pour des attributs variables (caractéristiques d’un produit, préférences d’un utilisateur), pas pour remplacer des colonnes interrogées en permanence.

SELECT nom,
       attributs -> 'taille'   AS taille_json,   -- résultat jsonb : "M"
       attributs ->> 'couleur' AS couleur        -- résultat text  : noir
FROM produits
WHERE attributs @> '{"couleur": "noir"}';        -- contient cette paire

->> renvoie du texte, à utiliser pour comparer ou afficher ; @> teste l’inclusion et peut utiliser un index GIN (leçon 4). SQLite propose aussi -> et ->> (depuis la version 3.38), ce qui permet de s’entraîner dans le navigateur.

Upsert et RETURNING

Insérer une ligne, ou la mettre à jour si elle existe déjà, se fait en une seule instruction atomique :

INSERT INTO stocks (produit_id, quantite)
VALUES (42, 10)
ON CONFLICT (produit_id)                       -- exige une contrainte UNIQUE ou PK
DO UPDATE SET quantite = stocks.quantite + EXCLUDED.quantite
RETURNING produit_id, quantite;

EXCLUDED désigne la ligne qu’on tentait d’insérer. ON CONFLICT DO NOTHING ignore simplement les doublons. RETURNING renvoie les lignes écrites (avec leurs valeurs générées : id, cree_le), ce qui évite un SELECT supplémentaire après chaque INSERT. Pour des synchronisations plus complexes, PostgreSQL dispose aussi de MERGE (depuis la version 15).

Premiers pas dans psql

psql est le client en ligne de commande officiel :

psql -h localhost -U app -d boutique   -- connexion
\dt                -- lister les tables
\d commandes       -- structure, index et contraintes d'une table
\x                 -- affichage étendu (une colonne par ligne)
\timing            -- afficher la durée de chaque requête
\q                 -- quitter

\d et \timing vous serviront dans toute la suite du cours pour vérifier vos index et mesurer vos optimisations.