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.

it seems you are running this query from psql console. use PgAgent client for this — Ilesh Patel, Apr 10 '14 at 13:57
I like this answer better https://stackoverflow.com/questions/42404579/use-psqls-copy-for-a-multi-line-query#42405094 — giorgio, Sep 09 '19 at 08:36

score 3 · Accepted Answer · answered Apr 11 '14 at 11:04

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.

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

1 Answers1