Skip site navigation (1) Skip section navigation (2)

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

From: Julia Jacobson <julia(dot)jacobson(at)arcor(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: psql '\copy' command for writing binary data from BYTEA column to file
Date: 2010-09-04 16:24:36
Message-ID: 4C8272C4.1000008@arcor.de (view raw or flat)
Thread:
Lists: pgsql-general
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

Responses

pgsql-general by date

Next:From: KurtDate: 2010-09-05 08:31:24
Subject: does record_eq() ignore user-defined operators?
Previous:From: Tom LaneDate: 2010-09-04 16:10:18
Subject: Re: How to restore a Plan from a stored plan text?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group