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?

2 Answers 2


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):


The output is appended to the file.

  • 1
    Or, for example, copy ... to program 'cat >> file.csv'
    – Abelisto
    Mar 13, 2017 at 23:55
  • 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?
    – Zeruno
    Mar 17, 2017 at 0:53

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.