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 ~/?
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
