Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Sat, Sep 4, 2010 at 12:24 PM, Julia Jacobson <julia.jacobson@xxxxxxxx> 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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux