Re: proposal: copybytea command for psql

A while ago I went looking for nice ways to export an unencoded bytea
value using psql, see
<http://people.planetpostgresql.org/andrew/index.php?/archives/196-Clever-trick-challenge.html>.
Regina Obe is also in want of a solution for this:
<http://www.postgresonline.com/journal/archives/243-PSQL-needs-a-better-way-of-outputting-bytea-to-binary-files.html>.

It seems like what we need is a psql command for it, something like:

\copybytea (select query_returning_one_bytea) to /path/to/file

Does anyone have a better solution or any objection to this feature?

cheers

andrew

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> A while ago I went looking for nice ways to export an unencoded bytea
> value using psql, see
> <http://people.planetpostgresql.org/andrew/index.php?/archives/196-Clever-trick-challenge.html>.
> Regina Obe is also in want of a solution for this:
> <http://www.postgresonline.com/journal/archives/243-PSQL-needs-a-better-way-of-outputting-bytea-to-binary-files.html>.

> It seems like what we need is a psql command for it, something like:
> \copybytea (select query_returning_one_bytea) to /path/to/file
> Does anyone have a better solution or any objection to this feature?

It seems awfully narrow. In the first place, why restrict it to bytea?
In the second, that syntax is going to cause serious headaches, not
least because backslash commands can't extend across multiple lines.

The idea that comes to mind for me, if you want to connect this up to
SELECT and not COPY, is some variant of \g that implies (1) pull back
the data as binary not text, and (2) dump it to the target file with
absolutely no recordseps, fieldseps, etc; just the bytes, ma'am.

It might be worth thinking of (1) and (2) as separately invokable
features, or then again it might not. I also wonder how this might
interact with Peter E's recent commit for null-byte separators.

regards, tom lane

On 02/16/2012 03:32 PM, Tom Lane wrote:
> Andrew Dunstan<andrew(at)dunslane(dot)net> writes:
>> A while ago I went looking for nice ways to export an unencoded bytea
>> value using psql, see
>> <http://people.planetpostgresql.org/andrew/index.php?/archives/196-Clever-trick-challenge.html>.
>> Regina Obe is also in want of a solution for this:
>> <http://www.postgresonline.com/journal/archives/243-PSQL-needs-a-better-way-of-outputting-bytea-to-binary-files.html>.
>> It seems like what we need is a psql command for it, something like:
>> \copybytea (select query_returning_one_bytea) to /path/to/file
>> Does anyone have a better solution or any objection to this feature?
> It seems awfully narrow. In the first place, why restrict it to bytea?
> In the second, that syntax is going to cause serious headaches, not
> least because backslash commands can't extend across multiple lines.
>
> The idea that comes to mind for me, if you want to connect this up to
> SELECT and not COPY, is some variant of \g that implies (1) pull back
> the data as binary not text, and (2) dump it to the target file with
> absolutely no recordseps, fieldseps, etc; just the bytes, ma'am.
>
> It might be worth thinking of (1) and (2) as separately invokable
> features, or then again it might not. I also wonder how this might
> interact with Peter E's recent commit for null-byte separators.
>
>

Oh, nice idea. say \g{bn} where b was for binary fetch/output and n was
for no recordseps etc?

That looks like a winner.

cheers

andrew

On tor, 2012-02-16 at 16:11 -0500, Andrew Dunstan wrote:
> Oh, nice idea. say \g{bn} where b was for binary fetch/output and n
> was for no recordseps etc?

Why not set the record separator to ''?