Import et export des champs BLOB dans PostgreSQL

Il y a deux méthodes de stockage des binaires dans des champs BLOB au niveau de PostgreSQL. La  1ère consiste à créer un champ de type OID, qui stockera la référence au fichier contenant les données binaires. Dans ce mode, PostgreSQL fait une copie du fichier dans le tablespace de la base. La 2e est de créer un champ BYTEA, qui contiendra le contenu du fichier binaire. La donnée est intégrée à la table !

La table d’exemple

Je crée tout d’abord une table comprenant trois champs :

CREATE TABLE public.affiche
 (
 affiche_nom character VARYING(1024),
 affiche_image_oid OID,
 affiche_image_bytea BYTEA
 );

Création du lien symbolique

Sous Windows, à partir du dossier PGDATA (C:\Program Files\PostgreSQL\9.6\data), je crée le lien symbolique images qui pointe vers le dossier D:\EXPIMP :

MKLINK /D images d:\expimp

Et oui, on peut aussi créer des liens symboliques sous Windows !

Chargement des images

Les méthodes pour charger les images en mode OID et BYTEA ne sont pas les mêmes : lo_import pour la 1ère gère les chemins absolus ; pg_read_binary pour la seconde n’autorise que les chemins relatifs à partir de PGDATA. Dans les deux cas, le compte de processus de PostgreSQL doit disposer des droits en lecture sur le répertoire.

INSERT INTO affiche VALUES ('Le prénom', lo_import('d:\expimp\le-prenom.jpg'),pg_read_binary_file('images/le-prenom.jpg'));

Export des images

En mode OID, la commande d’exportation du fichier est relativement simple. Quel que soit le mode retenu de stockage, vous devez disposer des droits en écriture sur D:\EXPIMP pour le compte de processus qui lance PostgreSQL !

SELECT lo_export(affiche_image_oid, 'd:\expimp\le-prenom-oid.jpg') 
FROM affiche 
WHERE affiche_nom = 'Le prénom';

En mode BYTEA, vous devrez recourir au programme xxd.exe. Sur Windows, afin de disposer de cet exécutable, il faut installer gvim et copier le fichier xxd.exe contenu dans C:\Program Files (x86)\Vim\vim80 vers c:\windows, par exemple.

COPY (SELECT encode(affiche_image_bytea,'hex') FROM affiche WHERE affiche_nom = 'Le prénom') 
 TO PROGRAM 'xxd -p -r > d:\expimp\le-prenom-bytea.jpg';

Vsou pouvez vérifier que les trois fichiers (l’original, l’export OID, l’export BYTEA) ont exactement la même taille à l’octet près.

Import et export des champs BLOB dans PostgreSQL

 

PostgreSQL  / DML Formateur PL/pgSQL Formateur PostgreSQL Formateur SQL Formation PostgreSQL PL/pgSQL PostgreSQL SQL 

Commentaires

Bonjour,
J’ai essayé de suivre votre démarche mais cela n’a malheureusement pas fonctionné.
J’utilise PostgreSQL comme base de données qui est herbergée sur un serveur dédié OVH sous Debian9. J’ai donc créé une table comme vous, puis j’ai inséré des éléments dans ma table. Mon image se trouve directement sous le serveur dans un répertoire que j’ai crée (/Images_DB/P1250420.JPG). Lorsque que je lance la requête, j’ai ça comme message : INSERT 0 1 Query returned successfully in 249 msec.
Mais quand j’ouvre ma table, il a mis des chiffres à la place de l’image : 196823, et quand je la lie à une donnée géométrique pour voir ce que ça donne dans QGIS et bien je ne vois pas du tout mon image !
Merci par avance pour votre aide,

Laisser un commentaire

(requis)

(requis)