BYTEA Feld in Datei schreiben

Liebe PostgreSQL Nutzer,

Mit Hilfe von psql würde ich gerne binäre Daten aus einem Feld des Typs BYTEA in eine Datei schreiben.
Dazu verwende ich folgenden Befehl:
“\copy BINARY (SELECT picfile FROM myphotos WHERE picture_id=‘3’) TO file.jpg”
Die Datei auf dem Client ist aber nicht lesbar, d.h. fehlerhaft.
Das Auslesen mit einem Python Skript funktioniert, weshalb ich glaube, dass der Fehler in meiner Verwendung von psql liegt.
Ich verwende PostgreSQL 8.3 (sowohl auf Server als auch auf Client) und Windows XP auf dem Client. Das Encoding der Datenbank ist UTF8.

Vielen Dank bereits im Voraus für eure Hilfe!

Durch lange Web-Suche habe ich nun wahrscheinlich zumindest den Grund dafür gefunden, warum das oben angegebene Vorgehen so nicht funktioniert.
Unter PostgreSQL 8.4 - COPY steht über den COPY Implementierung in PostgreSQL, die soweit ich das verstehe ähnlich von psql verwendet wird:
“The file format used for COPY BINARY changed in PostgreSQL 7.4. The new format consists of a file header, zero or more tuples containing the row data, and a file trailer. Headers and data are now in network byte order.
The file header consists of 15 bytes of fixed fields, followed by a variable-length header extension area.
The file trailer consists of a 16-bit integer word containing -1. This is easily distinguished from a tuple’s field-count word.
A reader should report an error if a field-count word is neither -1 nor the expected number of columns. This provides an extra check against somehow getting out of sync with the data.”
Die Frage ist nun, wie man nur die eigentlichen binären Daten ohne Header und Trailer bekommt.

Wenn Du nicht-PG Tools einsetzen kannst/willst, dann schau Dir mal SQL Workbench/J an.
Das hat extra Befehle um z.B. Binärdaten zu exportieren (einzeln oder eine ganze Tabelle)

Hallo castorp,
Vielen Dank für deine Hinweis auf SQL-Workbench, das ich seit einiger Zeit auch verwende.
Es würde mich aber trotzdem interessieren, wie man mit psql \copy Befehl bzw. der COPY Implementierung von PostgreSQL binäre Daten in Dateien kopiert, da vor allem letzteres den Vorteil hätte, in Skripten für die Administration (für COPY werden ja superuser Rechte benötigt) eingesetzt werden zu können.

Ja dann :wink:

in Skripten für die Administration (für COPY werden ja superuser Rechte benötigt) eingesetzt werden zu können.

Wenn’s gar nicht mit Copy geht: Die Workbench ist auch batch-fähig…

COPY ist nicht dafür gedacht, einzelne Dateien zu exportieren. Das kopiert Zeilen oder Tabellen auf eine schnelle Art und Weise in oder aus einer Datenbank.

Wenn COPY nicht die Lösung der Wahl für den Export von BYTEA Feldern in Dateien ist, was würdet ihr dann abgesehen von SQL-Workbench oder Python bzw. Perl Skripten dafür empfehlen?
Könnte man sich ein BYTEA Feld nicht im STDOUT anzeigen lassen und mit einer Pipe in eine Datei umleiten lassen?
Es gibt sicherlich mehrere Lösungen, aber ich suche eigentlich eine möglichst einfache; GUI Tools bieten mir eigentlich viel zu viele Features, und einen Interpreter für eine Skript Sprache möchte ich eigentlich auch nicht extra auf den Clients installieren.

Mir fällt dazu auf Anhieb keine Lösung ein.

Hmm, Du musst ja nicht die GUI von der Workbench verwenden:

java -jar sqlworkbench.jar
     -url='jdbc :p ostgresql://...'
     -driver=org.postgresql.Driver
     -driverjar=postgresql.jar 
     -username=postgres
     -password=postgres
     -command='WbSelectBlob blob_column INTO /path/to/file.data FROM the_table WHERE id = 42'

Zunächst einmal danke ich euch für eure freundliche Unterstützung.
Durch die exzellente Hilfe auf der PostgreSQL-Mailing-List und dem PostgreSQL IRC Channel, wurden mir zwei weitere Lösungswege gezeigt, die ich hier kurz für alle erkläre, die vor einem ähnlichen Problem wie ich stehen:

  1. Ausgabe über die Kommandozeile mittels psql:
    Man erzeugt einen hex-dump der Daten in der BYTEA Spalte “filecontent” aus der Tabelle “pictures” mit psql und transcodiert ihn mit dem Unix-Tool xxd in binäre Daten:
psql -tAqc "SELECT ENCODE(filecontent, 'hex') FROM pictures LIMIT 1" > picture_hex.txt
xxd -r -p picture_hex.txt > picture.jpg

Die Lösung ist funktioniert gut unter Linux auf dem Client.
Unter einem Client mit Windows fehlt mir das Wissen, wie eine Ausgabe mit Hilfe einer Pipe weiterleitet.
Außerdem habe ich mir nicht die Mühe gemacht, nach einem Tool für Windows zu suchen, das den hex-dump in binäre Daten konvertiert.

  1. Export über Binary Large Object (BLOB):
    Im Rahmen einer Transaktion wird ein BLOB erzeugt, wohin die Daten aus der BYTEA Spalte geschrieben werden. Anschließend wird dieser BLOB auf den Client exportiert und durch einen Rollback alle Änderungen in der Datenbank mit einem Rollback rückgängig gemacht.
    Die Variablen N und F im angegebenen Codebeispiel sind entsprechend der Ausgaben anzupassen:
begin;
SELECT lo_create(0);
SELECT lo_open(N, 131072); -- N ist hier die Ausgabe von lo_create
SELECT lowrite(F, filecontent) FROM pictures LIMIT 1; -- F ist hierbei die Ausgabe von lo_open
lo_export N TO picture.jpg
rollback;

Die mit ‘lowrite’ geschriebenen Daten werden mit dem Rollback sehr elegant wieder gelöscht, wogegen die auf dem Client geschiebene Datei vom Rollback unberührt bleibt.
Diese Lösung funktioniert unabhängig vom Betriebssystem des Clients.

Die erste Lösung gefällt mir gut :wink:

Das funktioniert unter Windows ganz genaus.

Außerdem habe ich mir nicht die Mühe gemacht, nach einem Tool für Windows zu suchen, das den hex-dump in binäre Daten konvertiert.

sfk (Swiss File Knife) kann das: Swiss File Knife - A Command Line Tools Collection for Windows (7/Vista/XP), Mac OS X, Linux and Raspberry Pi


Falls es jemanden interessiert, unter Windows sähe die Lösung z.B. so aus:

psql -tAqc "SELECT ENCODE(filecontent, 'hex') FROM pictures LIMIT 1" > picture_hex.txt
sfk filter picture_hex.txt +hextobin picture.jpg