I want the stupidest possible binary export

On 18 September 2014 16:06, David Rysdam <drysdam@ll.mit.edu> wrote:

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?

Hi,
first of all "with format binary" must be a part of the -c command, not
part of the shell command.

I don't know why this doesn't work:
psql -t -c "\copy (select mybinaryfield from mytable where key = 1) to
'file' with format binary "

but this works for me:
psql -t -c "\copy (select mybinaryfield from mytable where key = 1) to
'file' binary "

regards,
Szymon

On 09/18/2014 07:06 AM, David Rysdam wrote:

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

From here:

http://www.postgresql.org/docs/9.2/static/app-psql.html

the above should be:

psql -t -c "\copy (select mybinaryfield from mytable where key = 1) to
'file'" with 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?

--
Adrian Klaver
adrian.klaver@aklaver.com

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

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>写道：

Jov <amutu@amutu.com> writes:

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。

I wondered if I could do that. OK, will try it, thanks.

I use php and pg_unescape_bytea 

http://php.net/manual/en/function.pg-unescape-bytea.php

You also need to set bytea format to escaped in front of your query. 

If php can be en option ....

/Nicklas Avén

Skickat från min Samsung Mobil.

<div>-------- Originalmeddelande --------</div><div>Från: Jov <amutu@amutu.com> </div><div>Datum:2014-09-18 16:55 (GMT+01:00) </div><div>Till: David Rysdam <drysdam@ll.mit.edu>,pgsql-general <pgsql-general@postgresql.org> </div><div>Rubrik: Re: [GENERAL] I want the stupidest possible binary export </div><div>
</div>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

Jov <amutu@amutu.com> writes:

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。

This worked, btw. Encoded to base64, piped to sed to fix the newlines,
piped to 'base64 -id' and then to file.