From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Julia Jacobson <julia(dot)jacobson(at)arcor(dot)de> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: psql '\copy' command for writing binary data from BYTEA column to file |
Date: | 2010-09-09 16:25:20 |
Message-ID: | AANLkTi=2dARHQPRGTXmCx0aus9maHQ2SfxxbvihtuzDv@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2010-09-09 16:49:23 | Re: NOT IN vs. OUTER JOIN and NOT NULL |
Previous Message | Richard Huxton | 2010-09-09 15:52:30 | Re: Regular expression in an if-statement will not work |