Re: I want the stupidest possible binary export - Mailing list pgsql-general

From Nicklas Avén
Subject Re: I want the stupidest possible binary export
Date
Msg-id r5qedxa7q5kt2qydwxl1sca2.1411053536439@email.android.com
Whole thread Raw
In response to I want the stupidest possible binary export  (David Rysdam <drysdam@ll.mit.edu>)
List pgsql-general
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

pgsql-general by date:

Previous
From: David Rysdam
Date:
Subject: Re: I want the stupidest possible binary export
Next
From: John R Pierce
Date:
Subject: Re: Why isn't Java support part of Postgresql core?