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
