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