🔝 Retour au Sommaire

4.4.6. Types Binaires (BYTEA) et XML

Introduction

PostgreSQL offre des types de données pour stocker des contenus spécialisés : les données binaires brutes et les documents XML. Ces types sont moins courants dans les applications quotidiennes mais essentiels pour certains cas d'usage spécifiques.

Dans cette section, nous allons explorer :

BYTEA : Stockage de données binaires (images, fichiers, cryptographie)
XML : Stockage et manipulation de documents XML

Note importante : Pour la plupart des cas d'usage modernes, d'autres solutions sont souvent préférées (stockage de fichiers externe, JSON au lieu de XML). Cependant, ces types restent utiles dans des contextes spécifiques.

1. BYTEA (Données Binaires)

Qu'est-ce que BYTEA ?

BYTEA (Binary Data) est un type de données qui permet de stocker des séquences d'octets bruts (données binaires). C'est l'équivalent PostgreSQL d'un BLOB (Binary Large Object).

Utilisation typique :

Fichiers binaires (images, PDFs, documents)
Données cryptographiques (clés, hashes, signatures)
Données sérialisées
Données brutes de capteurs

Formats d'Encodage

PostgreSQL utilise deux formats pour représenter BYTEA :

Format	Description	Exemple
hex	Hexadécimal (défaut depuis PG 9.0)	`\x48656c6c6f`
escape	Format d'échappement legacy	`Hello` avec échappements

Format recommandé : hex (plus lisible et sans ambiguïté)

-- Voir le format d'encodage actuel
SHOW bytea_output;  -- hex ou escape

Création et Insertion

CREATE TABLE fichiers (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(255) NOT NULL,
    type_mime VARCHAR(100),
    contenu BYTEA NOT NULL,
    taille INTEGER,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insertion avec format hexadécimal
-- 'Hello' en ASCII/UTF-8
INSERT INTO fichiers (nom, type_mime, contenu, taille) VALUES
    ('hello.txt', 'text/plain', '\x48656c6c6f'::BYTEA, 5);

-- Insertion avec texte (converti automatiquement)
INSERT INTO fichiers (nom, type_mime, contenu, taille) VALUES
    ('test.txt', 'text/plain', 'Contenu texte'::BYTEA, 13);

-- Insertion avec decode depuis base64
INSERT INTO fichiers (nom, type_mime, contenu, taille) VALUES
    ('data.bin', 'application/octet-stream',
     decode('SGVsbG8gV29ybGQ=', 'base64'), 11);

SELECT id, nom, type_mime, taille FROM fichiers;

Lecture et Récupération

-- Récupérer le contenu brut (format hex)
SELECT contenu FROM fichiers WHERE nom = 'hello.txt';
-- Résultat : \x48656c6c6f

-- Convertir en texte (si le contenu est du texte)
SELECT convert_from(contenu, 'UTF8') AS texte  
FROM fichiers  
WHERE nom = 'hello.txt';  
-- Résultat : 'Hello'

-- Encoder en base64 pour transmission
SELECT encode(contenu, 'base64') AS base64  
FROM fichiers  
WHERE nom = 'hello.txt';  
-- Résultat : 'SGVsbG8='

-- Voir la taille en octets
SELECT
    nom,
    octet_length(contenu) AS taille_octets,
    length(contenu) AS taille_octets_alt
FROM fichiers;

Fonctions de Manipulation BYTEA

Encodage et Décodage

-- decode : Décoder depuis différents formats
SELECT decode('48656c6c6f', 'hex');  -- Depuis hexadécimal  
SELECT decode('SGVsbG8=', 'base64');  -- Depuis base64  
SELECT decode('Hello', 'escape');  -- Depuis format escape  

-- encode : Encoder vers différents formats
SELECT encode('\x48656c6c6f'::BYTEA, 'hex');  -- Vers hexadécimal  
SELECT encode('\x48656c6c6f'::BYTEA, 'base64');  -- Vers base64  
SELECT encode('\x48656c6c6f'::BYTEA, 'escape');  -- Vers escape  

-- Exemple pratique : stocker une image base64
CREATE TABLE images (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(255),
    data BYTEA
);

INSERT INTO images (nom, data) VALUES (
    'logo.png',
    decode('iVBORw0KGgoAAAANSUhEUgAAAAEAAAABCAYAAAAfFcSJAAAADUlEQVR42mNk+M9QDwADhgGAWjR9awAAAABJRU5ErkJggg==', 'base64')
);

-- Récupérer en base64 pour affichage web
SELECT
    nom,
    'data:image/png;base64,' || encode(data, 'base64') AS data_url
FROM images;

Conversions Texte-Binaire

-- convert_to : Texte vers BYTEA
SELECT convert_to('Bonjour', 'UTF8');  
SELECT convert_to('Hello', 'LATIN1');  

-- convert_from : BYTEA vers texte
SELECT convert_from('\x426f6e6a6f7572'::BYTEA, 'UTF8');  -- 'Bonjour'

-- Exemple : stocker du texte en différents encodages
CREATE TABLE textes_encodes (
    id SERIAL PRIMARY KEY,
    texte_original TEXT,
    encodage VARCHAR(20),
    donnees BYTEA
);

INSERT INTO textes_encodes (texte_original, encodage, donnees) VALUES
    ('Français', 'UTF8', convert_to('Français', 'UTF8')),
    ('Français', 'LATIN1', convert_to('Français', 'LATIN1'));

SELECT
    texte_original,
    encodage,
    donnees,
    convert_from(donnees, encodage) AS texte_decode
FROM textes_encodes;

Opérations sur BYTEA

-- Concaténation
SELECT '\x4865'::BYTEA || '\x6c6c6f'::BYTEA;  -- \x48656c6c6f ('Hello')

-- Longueur
SELECT octet_length('\x48656c6c6f'::BYTEA);  -- 5 octets

-- Extraction de sous-chaîne
SELECT substring('\x48656c6c6f'::BYTEA FROM 1 FOR 2);  -- \x4865 ('He')

-- Position
SELECT position('\x6c6c'::BYTEA IN '\x48656c6c6f'::BYTEA);  -- 3 ('ll' commence à l'octet 3)

-- Comparaison
SELECT '\x48656c6c6f'::BYTEA = '\x48656c6c6f'::BYTEA;  -- true  
SELECT '\x48656c6c6f'::BYTEA < '\x48656c6c70'::BYTEA;  -- true (comparaison lexicographique)

Fonctions Cryptographiques (Extension pgcrypto)

-- Activer l'extension pgcrypto
CREATE EXTENSION IF NOT EXISTS pgcrypto;

-- Hachage MD5
SELECT md5('password');  -- Retourne texte hexadécimal

-- Hachage SHA-256 (retourne BYTEA)
SELECT digest('password', 'sha256');  
SELECT encode(digest('password', 'sha256'), 'hex');  -- En hexadécimal  

-- Hachage SHA-512
SELECT encode(digest('password', 'sha512'), 'hex');

-- HMAC (Hash-based Message Authentication Code)
SELECT encode(
    hmac('message', 'secret_key', 'sha256'),
    'hex'
);

-- Génération de nombres aléatoires
SELECT gen_random_bytes(16);  -- 16 octets aléatoires  
SELECT encode(gen_random_bytes(32), 'hex');  -- 32 octets en hex

Cas d'Usage de BYTEA

1. Stockage de Petits Fichiers

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    titre VARCHAR(300),
    nom_fichier VARCHAR(255),
    type_mime VARCHAR(100),
    contenu BYTEA,
    taille INTEGER,
    uploaded_by INTEGER,
    uploaded_at TIMESTAMPTZ DEFAULT NOW()
);

-- Fonction pour calculer la taille
CREATE OR REPLACE FUNCTION set_taille()  
RETURNS TRIGGER AS $$  
BEGIN  
    NEW.taille = octet_length(NEW.contenu);
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_set_taille  
BEFORE INSERT OR UPDATE ON documents  
FOR EACH ROW  
EXECUTE FUNCTION set_taille();  

-- Insertion d'un document
INSERT INTO documents (titre, nom_fichier, type_mime, contenu, uploaded_by) VALUES (
    'Facture Novembre 2025',
    'facture_nov_2025.pdf',
    'application/pdf',
    decode('JVBERi0xLjQKJeLjz9MK...', 'base64'),  -- Contenu PDF encodé
    1
);

-- Statistiques de stockage
SELECT
    COUNT(*) AS nombre_fichiers,
    pg_size_pretty(SUM(taille)::BIGINT) AS taille_totale,
    AVG(taille) AS taille_moyenne
FROM documents;

⚠️ Limite importante : BYTEA peut stocker jusqu'à 1 Go, mais pour des fichiers volumineux, il est recommandé d'utiliser un stockage externe (S3, système de fichiers) et de stocker uniquement le chemin en base.

2. Hachage de Mots de Passe

CREATE TABLE utilisateurs_secure (
    id SERIAL PRIMARY KEY,
    username VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash BYTEA NOT NULL,  -- Hash du mot de passe
    salt BYTEA NOT NULL,  -- Salt unique par utilisateur
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Fonction pour créer un utilisateur avec hash sécurisé
CREATE OR REPLACE FUNCTION create_user(
    p_username VARCHAR,
    p_email VARCHAR,
    p_password TEXT
) RETURNS INTEGER AS $$
DECLARE
    v_salt BYTEA;
    v_hash BYTEA;
    v_user_id INTEGER;
BEGIN
    -- Générer un salt aléatoire
    v_salt := gen_random_bytes(32);

    -- Hasher le mot de passe avec le salt
    v_hash := digest(v_salt || convert_to(p_password, 'UTF8'), 'sha256');

    -- Insérer l'utilisateur
    INSERT INTO utilisateurs_secure (username, email, password_hash, salt)
    VALUES (p_username, p_email, v_hash, v_salt)
    RETURNING id INTO v_user_id;

    RETURN v_user_id;
END;
$$ LANGUAGE plpgsql;

-- Fonction pour vérifier un mot de passe
CREATE OR REPLACE FUNCTION verify_password(
    p_username VARCHAR,
    p_password TEXT
) RETURNS BOOLEAN AS $$
DECLARE
    v_stored_hash BYTEA;
    v_salt BYTEA;
    v_computed_hash BYTEA;
BEGIN
    -- Récupérer le hash et le salt
    SELECT password_hash, salt
    INTO v_stored_hash, v_salt
    FROM utilisateurs_secure
    WHERE username = p_username;

    IF NOT FOUND THEN
        RETURN FALSE;
    END IF;

    -- Calculer le hash du mot de passe fourni
    v_computed_hash := digest(v_salt || convert_to(p_password, 'UTF8'), 'sha256');

    -- Comparer
    RETURN v_stored_hash = v_computed_hash;
END;
$$ LANGUAGE plpgsql;

-- Utilisation
SELECT create_user('alice', 'alice@example.com', 'motdepasse123');  
SELECT verify_password('alice', 'motdepasse123');  -- true  
SELECT verify_password('alice', 'mauvais');  -- false

Note : Pour du vrai code de production, utilisez des bibliothèques spécialisées comme bcrypt, scrypt ou Argon2 plutôt que SHA-256 simple.

3. Signatures et Certificats

CREATE TABLE certificats (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(200),
    certificat BYTEA,  -- Certificat X.509
    cle_privee BYTEA,  -- Clé privée (chiffrée!)
    date_expiration DATE,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Stocker un certificat
INSERT INTO certificats (nom, certificat, date_expiration) VALUES (
    'SSL Certificate - example.com',
    decode('LS0tLS1CRUdJTiBDRVJUSUZJQ0FURS0tLS0t...', 'base64'),
    '2026-12-31'
);

-- Vérifier les certificats arrivant à expiration
SELECT nom, date_expiration  
FROM certificats  
WHERE date_expiration < CURRENT_DATE + INTERVAL '30 days'  
ORDER BY date_expiration;

4. Données Sérialisées Binaires

CREATE TABLE donnees_serialisees (
    id SERIAL PRIMARY KEY,
    type VARCHAR(50),
    payload BYTEA,  -- Données sérialisées (protobuf, msgpack, etc.)
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Exemple : stocker des données binaires d'un protocole personnalisé
INSERT INTO donnees_serialisees (type, payload) VALUES
    ('sensor_data', '\x010203040506070809'::BYTEA);

Limites et Bonnes Pratiques avec BYTEA

Limites

Taille maximale : Environ 1 Go (limite pratique)
Performance : Stocker de gros fichiers en base ralentit les sauvegardes et les requêtes
Mémoire : Les données BYTEA sont chargées entièrement en mémoire

Bonnes Pratiques

-- ✅ BON : Petits fichiers (< 1 MB)
CREATE TABLE avatars (
    user_id INTEGER PRIMARY KEY,
    image BYTEA CHECK (octet_length(image) <= 1048576)  -- Max 1 MB
);

-- ✅ BON : Données cryptographiques
CREATE TABLE api_keys (
    key_hash BYTEA,  -- Hash de la clé API
    key_encrypted BYTEA  -- Clé chiffrée
);

-- ❌ MAUVAIS : Gros fichiers
-- Ne stockez pas des vidéos, grandes images, etc. en BYTEA
-- Utilisez un stockage externe (S3, CDN) et stockez l'URL

-- ✅ RECOMMANDÉ : Référence externe
CREATE TABLE videos (
    id SERIAL PRIMARY KEY,
    titre VARCHAR(300),
    url VARCHAR(500),  -- URL vers S3/CDN
    thumbnail BYTEA  -- Seulement la miniature en base
);

Quand Utiliser BYTEA

✅ Utiliser BYTEA pour :

Petits fichiers (< 1 MB)
Hashes cryptographiques
Clés et certificats
Données binaires de petite taille
Données nécessitant transaction ACID

❌ Ne PAS utiliser BYTEA pour :

Fichiers volumineux (> 10 MB)
Contenus multimédias (vidéos, grandes images)
Fichiers fréquemment téléchargés
Très grand nombre de fichiers

Alternative recommandée : Stockage externe (AWS S3, Azure Blob, Google Cloud Storage) + URL en base.

2. XML (Extensible Markup Language)

Qu'est-ce que XML ?

XML (Extensible Markup Language) est un format de données structuré basé sur des balises, similaire à HTML mais plus strict et extensible.

Exemple de XML :

<utilisateur>
    <nom>Alice Martin</nom>
    <email>alice@example.com</email>
    <age>30</age>
    <adresse>
        <rue>123 rue de la Paix</rue>
        <ville>Paris</ville>
        <code_postal>75001</code_postal>
    </adresse>
    <hobbies>
        <hobby>Lecture</hobby>
        <hobby>Cyclisme</hobby>
    </hobbies>
</utilisateur>

XML vs JSON

Aspect	XML	JSON
Syntaxe	Verbose (balises)	Concise
Lisibilité	Moyenne	Excellente
Support PostgreSQL	Type XML natif	JSONB recommandé
Cas d'usage moderne	Legacy, échanges B2B	Standard moderne
Performance	Plus lent	Plus rapide

Note importante : Pour les nouveaux projets, JSON/JSONB est généralement préféré à XML. XML reste utile pour :

Intégration avec systèmes legacy
Échanges standardisés (SOAP, RSS, SVG)
Documents complexes avec schémas

Type XML dans PostgreSQL

CREATE TABLE documents_xml (
    id SERIAL PRIMARY KEY,
    titre VARCHAR(300),
    contenu XML,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Insertion de XML
INSERT INTO documents_xml (titre, contenu) VALUES (
    'Profil utilisateur',
    '<utilisateur>
        <nom>Alice Martin</nom>
        <email>alice@example.com</email>
        <age>30</age>
    </utilisateur>'
);

-- XML mal formé (erreur)
INSERT INTO documents_xml (titre, contenu) VALUES (
    'XML invalide',
    '<utilisateur><nom>Bob</utilisateur>'  -- Balise mal fermée
);
-- ERROR: invalid XML content

SELECT * FROM documents_xml;

Validation XML

PostgreSQL vérifie que le XML est well-formed (bien formé) :

Balises correctement ouvertes et fermées
Hiérarchie correcte
Caractères spéciaux échappés

-- XML valide
SELECT '<root><child>Texte</child></root>'::XML;

-- XML avec attributs
SELECT '<user id="123" active="true">Alice</user>'::XML;

-- XML avec espaces de noms
SELECT '<ns:element xmlns:ns="http://example.com">Contenu</ns:element>'::XML;

-- XML avec caractères spéciaux échappés
SELECT '<message>Price &lt; 100 &amp; Quality &gt; 5</message>'::XML;
-- < = &lt;  (less than)
-- > = &gt;  (greater than)
-- & = &amp; (ampersand)

-- Erreur : XML mal formé
SELECT '<unclosed>'::XML;  -- ERROR

Fonctions XML

Création de XML

-- xmlelement : Créer un élément
SELECT xmlelement(
    name "utilisateur",
    xmlelement(name "nom", 'Alice Martin'),
    xmlelement(name "email", 'alice@example.com')
);
-- Résultat : <utilisateur><nom>Alice Martin</nom><email>alice@example.com</email></utilisateur>

-- xmlforest : Créer plusieurs éléments
SELECT xmlforest(
    'Alice' AS prenom,
    'Martin' AS nom,
    30 AS age
);
-- Résultat : <prenom>Alice</prenom><nom>Martin</nom><age>30</age>

-- xmlconcat : Concaténer des éléments XML
SELECT xmlconcat(
    '<element1>Valeur1</element1>'::XML,
    '<element2>Valeur2</element2>'::XML
);

-- xmlcomment : Créer un commentaire
SELECT xmlcomment('Ceci est un commentaire');
-- Résultat : <!--Ceci est un commentaire-->

-- xmlpi : Créer une instruction de traitement
SELECT xmlpi(name php, 'echo "Hello"; ');
-- Résultat : <?php echo "Hello"; ?>

Générer XML depuis Tables

CREATE TABLE produits_xml (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(200),
    prix NUMERIC(10, 2),
    categorie VARCHAR(100)
);

INSERT INTO produits_xml (nom, prix, categorie) VALUES
    ('Ordinateur', 999.99, 'Électronique'),
    ('Souris', 29.99, 'Accessoires'),
    ('Clavier', 79.99, 'Accessoires');

-- Générer XML pour chaque ligne
SELECT xmlelement(
    name "produit",
    xmlattributes(id AS "id"),
    xmlelement(name "nom", nom),
    xmlelement(name "prix", prix),
    xmlelement(name "categorie", categorie)
) AS produit_xml
FROM produits_xml;

-- Résultat exemple :
-- <produit id="1"><nom>Ordinateur</nom><prix>999.99</prix><categorie>Électronique</categorie></produit>

-- Agréger en un seul document XML
SELECT xmlelement(
    name "catalogue",
    xmlagg(
        xmlelement(
            name "produit",
            xmlattributes(id AS "id"),
            xmlelement(name "nom", nom),
            xmlelement(name "prix", prix)
        )
    )
) AS catalogue_xml
FROM produits_xml;

-- Résultat :
-- <catalogue>
--   <produit id="1"><nom>Ordinateur</nom><prix>999.99</prix></produit>
--   <produit id="2"><nom>Souris</nom><prix>29.99</prix></produit>
--   <produit id="3"><nom>Clavier</nom><prix>79.99</prix></produit>
-- </catalogue>

XPath : Requêter du XML

XPath est un langage de requête pour naviguer dans des documents XML.

-- Exemple de document XML
CREATE TABLE livres_xml (
    id SERIAL PRIMARY KEY,
    info XML
);

INSERT INTO livres_xml (info) VALUES (
    '<livre>
        <titre>PostgreSQL: Up and Running</titre>
        <auteurs>
            <auteur>Regina Obe</auteur>
            <auteur>Leo Hsu</auteur>
        </auteurs>
        <prix devise="USD">39.99</prix>
        <pages>428</pages>
    </livre>'
);

-- xpath : Extraire des valeurs avec XPath
SELECT xpath('/livre/titre/text()', info) AS titre  
FROM livres_xml;  
-- Résultat : {PostgreSQL: Up and Running}

-- Extraire plusieurs éléments
SELECT xpath('/livre/auteurs/auteur/text()', info) AS auteurs  
FROM livres_xml;  
-- Résultat : {Regina Obe, "Leo Hsu"}

-- Extraire un attribut
SELECT xpath('/livre/prix/@devise', info) AS devise  
FROM livres_xml;  
-- Résultat : {USD}

-- Extraire une valeur numérique et la convertir
SELECT (xpath('/livre/prix/text()', info))[1]::TEXT::NUMERIC AS prix  
FROM livres_xml;  
-- Résultat : 39.99

XPath avec Prédicats

INSERT INTO livres_xml (info) VALUES (
    '<bibliotheque>
        <livre id="1">
            <titre>Livre A</titre>
            <prix>29.99</prix>
        </livre>
        <livre id="2">
            <titre>Livre B</titre>
            <prix>39.99</prix>
        </livre>
        <livre id="3">
            <titre>Livre C</titre>
            <prix>19.99</prix>
        </livre>
    </bibliotheque>'
);

-- Sélectionner un livre par ID
SELECT xpath('/bibliotheque/livre[@id="2"]/titre/text()', info)  
FROM livres_xml  
WHERE id = 2;  

-- Livres avec prix < 30
SELECT xpath('/bibliotheque/livre[prix < 30]/titre/text()', info)  
FROM livres_xml  
WHERE id = 2;  

-- Premier livre
SELECT xpath('/bibliotheque/livre[1]/titre/text()', info)  
FROM livres_xml  
WHERE id = 2;

Fonctions de Test XML

-- xmlexists : Vérifier si un élément existe
SELECT
    id,
    xmlexists('/livre/auteurs' PASSING info) AS a_auteurs
FROM livres_xml;

-- Filtrer avec xmlexists
SELECT * FROM livres_xml  
WHERE xmlexists('/livre[prix < 40]' PASSING info);  

-- Compter des éléments
SELECT
    (xpath('count(/livre/auteurs/auteur)', info))[1]::TEXT::INTEGER AS nombre_auteurs
FROM livres_xml  
WHERE id = 1;

Modification de XML

-- Mise à jour de contenu XML
UPDATE livres_xml  
SET info = '<livre>  
        <titre>PostgreSQL: The Definitive Guide</titre>
        <auteurs>
            <auteur>Regina Obe</auteur>
        </auteurs>
        <prix devise="EUR">35.00</prix>
        <pages>450</pages>
    </livre>'::XML
WHERE id = 1;

-- Extraction, modification et réinsertion
-- (XML n'a pas de fonctions de modification in-place comme JSONB)

Cas d'Usage de XML

1. Flux RSS/Atom

CREATE TABLE flux_rss (
    id SERIAL PRIMARY KEY,
    source VARCHAR(200),
    contenu XML,
    date_recuperation TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO flux_rss (source, contenu) VALUES (
    'Blog Tech',
    '<?xml version="1.0" encoding="UTF-8"?>
    <rss version="2.0">
        <channel>
            <title>Blog Tech</title>
            <link>https://blog.example.com</link>
            <description>Actualités technologiques</description>
            <item>
                <title>PostgreSQL 18 est sorti</title>
                <link>https://blog.example.com/pg18</link>
                <pubDate>Mon, 19 Nov 2025 10:00:00 GMT</pubDate>
                <description>Découvrez les nouveautés de PostgreSQL 18</description>
            </item>
        </channel>
    </rss>'
);

-- Extraire les articles
SELECT
    (xpath('/rss/channel/title/text()', contenu))[1]::TEXT AS flux_titre,
    unnest(xpath('/rss/channel/item/title/text()', contenu))::TEXT AS article_titre,
    unnest(xpath('/rss/channel/item/link/text()', contenu))::TEXT AS article_lien
FROM flux_rss;

2. Configuration et Paramètres

CREATE TABLE configurations_xml (
    id SERIAL PRIMARY KEY,
    application VARCHAR(100),
    environnement VARCHAR(20),
    config XML
);

INSERT INTO configurations_xml (application, environnement, config) VALUES (
    'web-app',
    'production',
    '<configuration>
        <database>
            <host>db.prod.example.com</host>
            <port>5432</port>
            <name>webapp_prod</name>
        </database>
        <cache>
            <enabled>true</enabled>
            <ttl>3600</ttl>
        </cache>
    </configuration>'
);

-- Lire la configuration
SELECT
    (xpath('/configuration/database/host/text()', config))[1]::TEXT AS db_host,
    (xpath('/configuration/database/port/text()', config))[1]::TEXT::INTEGER AS db_port,
    (xpath('/configuration/cache/enabled/text()', config))[1]::TEXT::BOOLEAN AS cache_enabled
FROM configurations_xml  
WHERE application = 'web-app' AND environnement = 'production';

3. Échanges B2B (EDI)

CREATE TABLE commandes_edi (
    id SERIAL PRIMARY KEY,
    numero_commande VARCHAR(50),
    partenaire VARCHAR(200),
    document XML,
    date_reception TIMESTAMPTZ DEFAULT NOW()
);

-- Commande EDI au format XML
INSERT INTO commandes_edi (numero_commande, partenaire, document) VALUES (
    'PO-2025-001',
    'Fournisseur ABC',
    '<purchaseOrder orderDate="2025-11-19">
        <shipTo>
            <name>Alice Martin</name>
            <street>123 rue de la Paix</street>
            <city>Paris</city>
            <zip>75001</zip>
        </shipTo>
        <items>
            <item partNum="ABC123">
                <productName>Widget</productName>
                <quantity>100</quantity>
                <price>9.99</price>
            </item>
        </items>
    </purchaseOrder>'
);

4. Documents SVG

CREATE TABLE graphiques_svg (
    id SERIAL PRIMARY KEY,
    nom VARCHAR(200),
    svg XML
);

INSERT INTO graphiques_svg (nom, svg) VALUES (
    'Cercle rouge',
    '<svg width="100" height="100" xmlns="http://www.w3.org/2000/svg">
        <circle cx="50" cy="50" r="40" fill="red"/>
    </svg>'
);

XML vs JSONB : Quand Utiliser Quoi ?

-- Même données en XML
SELECT '<user>
    <name>Alice</name>
    <email>alice@example.com</email>
    <age>30</age>
</user>'::XML;

-- Même données en JSONB (plus simple et moderne)
SELECT '{
    "name": "Alice",
    "email": "alice@example.com",
    "age": 30
}'::JSONB;

-- Requête XML (verbeux)
SELECT xpath('/user/name/text()', '<user><name>Alice</name></user>'::XML);

-- Requête JSONB (simple)
SELECT '{"name": "Alice"}'::JSONB ->> 'name';

Recommandation moderne :

Besoin	Recommandation
Nouveau projet	JSONB
API REST moderne	JSONB
Intégration legacy	XML
Standards B2B (EDI, SOAP)	XML
Flux RSS/Atom	XML
Documents SVG	XML
Configuration flexible	JSONB

Comparaison et Recommandations

Tableau Récapitulatif

Type	Taille Max	Cas d'Usage	Performance	Recommandation
BYTEA	~1 GB	Petits fichiers, crypto	Bonne	✅ Pour < 1 MB
XML	Illimitée	Legacy, B2B, RSS	Moyenne	⚠️ Préférer JSONB

Quand Utiliser Quoi ?

BYTEA

✅ Utiliser BYTEA pour :

Hashes et signatures cryptographiques
Petits fichiers (< 1 MB)
Clés et certificats
Données binaires nécessitant ACID

❌ Éviter BYTEA pour :

Fichiers volumineux (> 10 MB)
Contenus multimédias
Très grand nombre de fichiers

Alternative : Stockage externe (S3, CDN) + référence en base

XML

✅ Utiliser XML pour :

Intégration avec systèmes legacy XML
Standards B2B (EDI, SOAP)
Flux RSS/Atom
Documents SVG
Conformité à des schémas XML stricts

❌ Éviter XML pour :

Nouveaux projets (préférer JSONB)
API REST modernes
Applications web contemporaines

Alternative : JSONB pour données semi-structurées

Bonnes Pratiques

1. BYTEA : Limiter la Taille

-- ✅ BON : Contrainte de taille
CREATE TABLE avatars (
    user_id INTEGER PRIMARY KEY,
    image BYTEA CHECK (octet_length(image) <= 1048576)  -- Max 1 MB
);

-- ❌ MAUVAIS : Pas de limite
CREATE TABLE fichiers (
    contenu BYTEA  -- Peut accepter jusqu'à 1 GB !
);

2. BYTEA : Utiliser Stockage Externe pour Gros Fichiers

-- ✅ RECOMMANDÉ : Référence externe
CREATE TABLE videos (
    id SERIAL PRIMARY KEY,
    titre VARCHAR(300),
    fichier_url VARCHAR(500),  -- URL S3/CDN
    thumbnail BYTEA,  -- Seulement miniature en base
    duree INTERVAL
);

-- ❌ MAUVAIS : Stocker vidéo en base
CREATE TABLE videos_mauvais (
    contenu BYTEA  -- Vidéo de plusieurs GB en base !
);

3. BYTEA : Toujours Encoder pour Transmission

-- ✅ BON : Encoder en base64 pour API/web
SELECT encode(image, 'base64') FROM avatars;

-- ❌ MAUVAIS : Retourner BYTEA brut
SELECT image FROM avatars;  -- Données binaires brutes

4. XML : Préférer JSONB pour Nouveaux Projets

-- ❌ Nouveau projet avec XML
CREATE TABLE configurations (
    config XML
);

-- ✅ Nouveau projet avec JSONB
CREATE TABLE configurations (
    config JSONB
);

5. XML : Valider avec Schémas (XSD)

PostgreSQL ne valide pas les schémas XML (XSD) nativement. Pour validation stricte, validez côté application avant insertion.

6. Performances : Indexer Intelligemment

-- BYTEA : Indexer les hashes
CREATE INDEX idx_users_password_hash ON utilisateurs(password_hash);

-- XML : XPath ne peut pas utiliser d'index standard
-- Mieux : extraire et stocker séparément les valeurs fréquemment recherchées
CREATE TABLE documents_optimized (
    id SERIAL PRIMARY KEY,
    titre VARCHAR(300),  -- Extrait du XML
    contenu XML
);

CREATE INDEX idx_documents_titre ON documents_optimized(titre);

Récapitulatif

Types Spécialisés

BYTEA : Données binaires
- Formats : hex (recommandé), escape
- Fonctions : encode(), decode(), convert_to/from()
- Limite pratique : 1 MB
- Cas d'usage : crypto, petits fichiers
XML : Documents XML structurés
- Validation : well-formed automatique
- Requêtes : XPath
- Fonctions : xmlelement(), xpath(), xmlagg()
- Alternative moderne : JSONB

Commandes Essentielles

-- BYTEA
SELECT encode(data, 'base64');  
SELECT decode('SGVsbG8=', 'base64');  
SELECT digest('password', 'sha256');  

-- XML
SELECT xpath('/root/element/text()', xml_column);  
SELECT xmlelement(name "tag", 'contenu');  
SELECT xmlexists('/path' PASSING xml_column);

Conclusion

BYTEA et XML sont des types spécialisés pour des besoins spécifiques :

BYTEA reste utile pour les données cryptographiques et les petits fichiers, mais évitez de stocker de gros fichiers en base
XML est principalement utilisé pour l'intégration avec des systèmes legacy. Pour les nouveaux projets, JSONB est généralement préféré

Recommandations principales :

Utilisez BYTEA pour crypto et fichiers < 1 MB
Stockez les gros fichiers en externe (S3, CDN)
Préférez JSONB à XML pour les nouveaux projets
Gardez XML pour les standards B2B et flux RSS

Dans la prochaine section, nous explorerons les séquences et la génération automatique de valeurs pour les clés primaires.

⏭️ Séquences (SEQUENCE) et génération automatique

FilesExpand file tree

04.6-types-binaires-xml.md

Latest commit

History

04.6-types-binaires-xml.md

File metadata and controls

4.4.6. Types Binaires (BYTEA) et XML

Introduction

1. BYTEA (Données Binaires)

Qu'est-ce que BYTEA ?

Formats d'Encodage

Création et Insertion

Lecture et Récupération

Fonctions de Manipulation BYTEA

Encodage et Décodage

Conversions Texte-Binaire

Opérations sur BYTEA

Fonctions Cryptographiques (Extension pgcrypto)

Cas d'Usage de BYTEA

1. Stockage de Petits Fichiers

2. Hachage de Mots de Passe

3. Signatures et Certificats

4. Données Sérialisées Binaires

Limites et Bonnes Pratiques avec BYTEA

Limites

Bonnes Pratiques

Quand Utiliser BYTEA

2. XML (Extensible Markup Language)

Qu'est-ce que XML ?

XML vs JSON

Type XML dans PostgreSQL

Validation XML

Fonctions XML

Création de XML

Générer XML depuis Tables

XPath : Requêter du XML

XPath avec Prédicats

Fonctions de Test XML

Modification de XML

Cas d'Usage de XML

1. Flux RSS/Atom

2. Configuration et Paramètres

3. Échanges B2B (EDI)

4. Documents SVG

XML vs JSONB : Quand Utiliser Quoi ?

Comparaison et Recommandations

Tableau Récapitulatif

Quand Utiliser Quoi ?

BYTEA

XML

Bonnes Pratiques

1. BYTEA : Limiter la Taille

2. BYTEA : Utiliser Stockage Externe pour Gros Fichiers

3. BYTEA : Toujours Encoder pour Transmission

4. XML : Préférer JSONB pour Nouveaux Projets

5. XML : Valider avec Schémas (XSD)

6. Performances : Indexer Intelligemment

Récapitulatif

Types Spécialisés

Commandes Essentielles

Conclusion