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

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

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