Hello everybody out there using PostgreSQL,
One of the tables in my database contains pictures in jpeg format in a
column of the type BYTEA.
I have written a script in Python for inserting pictures into the
database as well as exporting them from the database later. It works fine.
When running psql in Windows XP (PostgreSQL Version 8.3 on client, same
on server) with the command
"\copy BINARY (SELECT picture FROM my_table LIMIT 1) TO picture.jpg;",
I get a corrupted image file, which can't be displayed or opened by any
software.
The official documentation
(http://www.postgresql.org/docs/8.3/interactive/sql-copy.html) contains
a nice explanation for that:
"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."
I mean, the insertion of a header and a trailer probably destroys the
jpeg format of the binary data.
Could anyone help me to find a way for writing the binary data from a
BYTEA field to a file on the client?
Thanks in advance,
Julia