As the title said. I found it inconvenient when accidentally rerun a copy (time-consuming query) to '\tmp\foo.csv'. I tried setting set -o noclobber in .bashrc but it did not help.
How can I prevent copy from overwriting existing file?
I am using Postgres 10. The doc seems not giving any info.
There is no such feature built into PostgreSQL.
You could use the PROGRAM option to do something like this:
copy pgbench_branches to program 'test -f /tmp/foo && exit 1|| cat > /tmp/foo';
But that is pretty undesirable as you have to enter the filename twice so it could get out of sync. And if you can't remember that you already exported the data, you probably can't remember to code it this way in the first place, unless it is encapsulated into a script.
If you compile your own server, it would be pretty easy to hack the server to have this feature (example for linux):
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 4f04d12..552fb81 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -1915,7 +1915,7 @@ BeginCopyTo(ParseState *pstate,
oumask = umask(S_IWGRP | S_IWOTH);
PG_TRY();
{
- cstate->copy_file = AllocateFile(cstate->filename, PG_BINARY_W);
+ cstate->copy_file = AllocateFile(cstate->filename, "wx");
}
PG_CATCH();
{
But running a customized server comes with its own cost (you have to reapply it to ever minor version update, you become reliant on features that don't exist outside your own set up, new people have to be trained on your peculiarities, etc.).
What I end up doing is creating a materialized view. This way accidentally losing query result is almost impossible, unless I drop the view. In the worst case, I can copy (select all from my_view) to '\foo.csv'.
