psql '\copy' command for writing binary data from BYTEA column to file

On Sat, Sep 4, 2010 at 12:24 PM, Julia Jacobson <julia.jacobson@arcor.de> wrote:

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,

There's a number of ways to do this. Probably the easiest is like this:

select encode(picture, 'hex') from my_table limit 1;

to get a hex encoded version of your picture. you can run that
through psql like this:

psql -tAqc "select encode(picture, 'hex') from my_table limit 1" >
picture_hex.txt

then you can unencode from hex using any number of utilities.

If you want a higher performance solution, the best approach is to
probably write a bit of C over libpq, setting the binary switch on the
result and immediately writing out the file yourself. If you are
prepared to do that, check the docs on libpq.

merlin