psql can only input／output text string，which can not be binary content。with 9.2，you can encode bytea to base64，save to file，then use shell command to decode the file。
google “amutu.com pg bytea” can get a blog post。
with upcoming 9.4，you can change bytea to large object，then use lo_* psql cmd save it to file。
2014年9月18日 10:09 PM于 "David Rysdam" <
drysdam@ll.mit.edu>写道：
I've got a some tables with bytea fields that I want to export only the
binary data to files. (Each field has a gzipped data file.)
I really want to avoid adding overhead to my project by writing a
special program to do this, so I'm trying to do it from psql. Omitting
the obvious switches for username, etc, here's what I'm doing:
psql -t -c "\copy (select mybinaryfield from mytable where key = 1) to
'file'"
That works, but I get escaped bytes. I want actual binary directly out
of the DB. Another option might be:
psql -t -c "\copy (select mybinaryfield from mytable where key = 1) to
'file'" with format binary
However, there are two problems. First, I get an syntax error "at or
near 'format'". (Running 9.2 client and server.) And second, I suspect
that'll be some "proprietary" PG format, not the actual bytes from just
my field.
What option am I missing?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general