Thread: 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
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 ''?