Using COPY in psql script does not write to file I have a script with multiple queries I am triying to write to a csv file:

copy (
   select /* very long query */
) to '/tmp/report.csv' with csv header; 


And I run the script like this 

psql -d mydb -f produce_reports.sql


The script seems to work but nothing is written to /tmp.

I usually use \copy but I am unable to use this in a script. From what I understand using \copy require having all the command on the same line which is not a viable option for me.

Any idea why COPY report success but don`t write anything?

P.S tried runnig with sudo, with psql, and tries granting mydb user superuser.
I usually use \copy but I am unable to use this in a script. From what I understand using \copy require having all the command on the same line which is not a viable option for me.


No all of the commands don't have to be on the same line, but that's not the problem here. COPY is saving the file on the server. You want it on the client, that's what the psql command \COPY does. I assume you're connecting to the remote server which is where the file is. Likely you could just scp or rsync it back to the client or the like, but that's a bad idea. You're better off doing it with just a client.

There are numerous ways to put the \copy on a different line. For instance, take this test.psql

CREATE TEMP TABLE t AS VALUES (1,2), (2,3);
\COPY t TO /tmp/1
CREATE TEMP TABLE b AS VALUES (1,2), (2,3);
\COPY t TO /tmp/2


You can run it with psql -d test -f test.psql


COPY is a PostgreSQL server-command that is made to look like SQL.
\COPY is a PostgreSQL client (psql)-command that is made to look like COPY