Thread: I want the stupidest possible binary export

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?

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

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。

Adrian Klaver <adrian.klaver@aklaver.com> writes:
>>      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

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 <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.

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。

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.