Using Postgres COPY TO for data export without overwriting file

Question

I'm running several queries with a script and psql. I am also exporting the results to a.csv using the COPY TO command. However, this overwrites the file a.csv when I would like to instead append the results to a.csv instead.

Is it possible using the COPY TO syntax perhaps there is some parameter I am missing? Or should I look at using other tools?

Answer 1

You could use the meta command \copy in psql (which encapsulates SQL COPY) on the command line, send it to stdout and and redirect output to be appended to a file - with >> instead of >:

psql -c '\copy (SELECT 1, 2) to stdout' >> /path/to/file_to_append_to.csv

(You probably need more connection parameters.)
You can use SQL COPY as well:

psql -c 'COPY (SELECT 1, 2) TO STDOUT' >> /path/to/file_to_append_to.csv

But it's often more convenient to use \copy instead because (quoting the manual):

file accessibility and access rights depend on the client rather than the server when \copy is used.

Or, like Abelisto commented, from inside psql or any other client - if the program cat is available (basically on all *nix OS):

COPY (SELECT 1, 2) TO PROGRAM 'cat >> /path/to/file_to_append_to.csv'

Or, still from inside psql, on any system, you can set the current directory and an output file with:

\cd /path/to/
\o file_to_append_to.csv

The file gets created (only) if it does not exist yet.
Then you can also SQL COPY (or still \copy as well):

COPY (SELECT 1, 2) TO STDOUT;

The output is appended to the file.

• Abelisto: Or, for example, copy ... to program 'cat >> file.csv'
• Zeruno: I have been tryinig both \copy and COPY ... to program 'C:\cat.exe >> C:\...\file.csv'. This works in psql terminal but when I am passing this to psql through a .sh script, the copy fails: COPY fails with "DETAIL: child process was terminated by exception 0xC0000135" and \copy replaces PROGRAM with STDOUT and complains of a syntax error in the proceeding ' i.e. "...TO STDOUT 'C:\...". What could be going on?

Answer 2

Exporting multiple queries to the same file using UNION and COPY

Just use COPY TO and a query.

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
 TO { 'filename' | PROGRAM 'command' | STDOUT }
 [ [ WITH ] ( option [, ...] ) ]

So something like this

COPY (
 SELECT * FROM generate_series(1,10) AS t(x)
 UNION ALL
 SELECT * FROM generate_series(10,20) AS t(x)
) TO '/tmp/foo.csv' CSV;