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
– Ilesh Patel, Commented Apr 10, 2014 at 13:57
I like this answer better stackoverflow.com/questions/42404579/… — giorgio
– giorgio, Commented Sep 9, 2019 at 8:36

Daniel Vérité · Accepted Answer · 2014-04-11 11:04:14Z

3

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.

answered Apr 11, 2014 at 11:04

Daniel Vérité

62.3k16 gold badges134 silver badges160 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related