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):
COPY (SELECT 1, 2) TO STDOUT;
The output is appended to the file.
-
1Or, for example,
copy ... to program 'cat >> file.csv'
Abelisto– Abelisto2017年03月13日 23:55:31 +00:00Commented 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– Zeruno2017年03月17日 00:53:37 +00:00Commented 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
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;