2

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?

6

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.

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

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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