PostgreSQL COPY command - how to write query in more than one line

Question

I'm using the PostgreSql database. I want to select some data from database and copy it in a csv file. And this works:

\COPY (SELECT * from table) TO '/csv_dir/csv_file.csv';

My problem is that it works only if the whole command is in one line only. How can I write the COPY command in more than one line? This sql is what I want to put in more rows.

Answer 1

As an internal command of psql, and like other commands starting with a backslash, \copy must fit on a single line.

I presume your purpose is to paste a long query with line breaks without having to edit it.

You may use COPY instead of \COPY, redirect its output to STDOUT, and redirect this stdout to a file. Example:

$ psql -At -d test <<EOQ >outfile
COPY
 (select 1,2
  union
  select 3,4)
TO STDOUT;
EOQ

Result:

$ cat outfile
1   2
3   4

If you're already in a psql session and this COPY must exist among other commands, it can also be achieved with this sequence:

test=> \t
Showing only tuples.
test=> \o outfile
test=> copy
test->  (select 1,2
test(> union
test(> select 3,4)
test-> to stdout;
test=> \o
test=> \t
Tuples only is off.

The \o outfile starts capturing the output into the file and \o without argument ends it.