Thread: I want the stupidest possible binary export

I want the stupidest possible binary export

From
David Rysdam
Date:
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?


Re: I want the stupidest possible binary export

From
Szymon Guz
Date:

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

Re: I want the stupidest possible binary export

From
Adrian Klaver
Date:
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


Re: I want the stupidest possible binary export

From
Jov
Date:

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

Re: I want the stupidest possible binary export

From
David Rysdam
Date:
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?

Attachment

Re: I want the stupidest possible binary export

From
David Rysdam
Date:
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.

Attachment

Re: I want the stupidest possible binary export

From
Nicklas Avén
Date:
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.


-------- Originalmeddelande --------
Från: Jov
Datum:2014-09-18 16:55 (GMT+01:00)
Till: David Rysdam ,pgsql-general
Rubrik: Re: [GENERAL] I want the stupidest possible binary export

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

Re: I want the stupidest possible binary export

From
David Rysdam
Date:
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.

Attachment