Re: COPY to question

On Tue, Jan 17, 2017 at 10:23 AM, Rich Shepard <rshepard(at)appl-ecosys(dot)com>
wrote:

> Running -9.6.1. I have a database created and owned by me, but cannot
> copy
> a table to my home directory. Postgres tells me it cannot write to that
> directory. The only way to copy tables to files is by doing so as the
> superuser (postgres).
>
> Why is this, and can I change something so I, as a user, can copy tables
> directly to ~/?
>
> To add to the other answers, more info is available at
https://www.postgresql.org/docs/current/static/app-psql.html#APP-PSQL-META-COMMANDS-COPY
and https://wiki.postgresql.org/wiki/COPY

Note that you can invoke SQL COPY to STDOUT as in: COPY (some arbitrary
query) TO STDOUT;

You would either pipe/redirect the output of psql as desired or use the
"\o" within psql to reroute the output to a file or pipe to a program, for
example, output to a CSV using a pipe as the delimiter and double-quote as
the quote character but change all "ma" to "pa" and put into myoutput.txt

\o | sed s/ma/pa/g > myoutput.txt
copy (some query) to stdout csv header delimiter '|' quote '"';
\o

Cheers,
Steve