6

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?

asked Mar 13, 2017 at 23:14

2 Answers 2

10

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.

answered Mar 13, 2017 at 23:25
2
  • 1
    Or, for example, copy ... to program 'cat >> file.csv' Commented 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? Commented Mar 17, 2017 at 0:53
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;
answered Mar 13, 2017 at 23:25

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.