I want the stupidest possible binary export

[David Rysdam]

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?

[Szymon Guz]

Post by David Rysdam
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
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
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

[Adrian Klaver]

Post by David Rysdam
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
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
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

Post by David Rysdam
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?

[David Rysdam]

Post by Adrian Klaver

Post by David Rysdam
psql -t -c "\copy (select mybinaryfield from mytable where key = 1) to
'file'" with format binary

http://www.postgresql.org/docs/9.2/static/app-psql.html
psql -t -c "\copy (select mybinaryfield from mytable where key = 1) to
'file'" with binary

OK, good, that works. However, now I have the problem I suspected I
would: I get more than just the bytes that were in my field. 27 bytes
more, to be exact.

I already did -t for tuples-only and I'm only doing one row and column
so there's no separators there. Since it's a .gz file and 'file' no
longer recognizes it, I deduce that the magic cookie isn't at the
beginning of the file anymore and some of the 27 bytes must be at the
beginning of the file instead.

Is this a PG-specific binary format with a wrapper around my data or
just my data only?

[Jov]

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¡£

Post by David Rysdam
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
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
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?
--
http://www.postgresql.org/mailpref/pgsql-general

[David Rysdam]

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.

[David Rysdam]

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.

[Nicklas Avén]

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.com> </div><div>Datum:2014-09-18 16:55 (GMT+01:00) </div><div>Till: David Rysdam <***@ll.mit.edu>,pgsql-general <pgsql-***@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" <***@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-***@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general