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

On Sat, Sep 4, 2010 at 12:24 PM, Julia Jacobson <julia(dot)jacobson(at)arcor(dot)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