Import et export des champs BLOB dans PostgreSQL
5 juillet 2017
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.
