Use psql's \copy for a multi-line query

Question

This is a follow-up question from this answer for "Save PL/pgSQL output from PostgreSQL to a CSV file".

I need to write a client-side CSV file using psql's \copy command. A one liner works:

db=> \copy (select 1 AS foo) to 'bar.csv' csv header
COPY 1

However, I have long queries that span several lines. I don't need to show the query, as I can't seem to extend this past one line without a parse error:

db=> \copy (
\copy: parse error at end of line
db=> \copy ( \\
\copy: parse error at end of line
db=> \copy ("
\copy: parse error at end of line
db=> \copy "(
\copy: parse error at end of line
db=> \copy \\
\copy: parse error at end of line

Is it possible to use \copy with a query that spans multiple lines? I'm using psql on Windows.

@Falmarri I've put a few common escape attempts in my question, but from the docs "psql's ... backslash escapes do not apply" to `\copy`, which makes things difficult. — Mike T, Feb 23 '17 at 00:09
I have posted an answer her: http://stackoverflow.com/a/44043716/24105 which is a roundabout way to get multiple lines. — Khaja Minhajuddin, May 18 '17 at 09:25

score 76 · Accepted Answer · answered Feb 23 '17 at 00:48

76

The working solution I have right now is to create a temporary view, which can be declared over multiple lines, then select from it in the \copy command, which fits comfortably on one line.

db=> CREATE TEMP VIEW v1 AS
db->   SELECT i
db->   FROM generate_series(1, 2) AS i;
CREATE VIEW
db=> \cd /path/to/a/really/deep/directory/structure/on/client
db=> \copy (SELECT * FROM v1) TO 'out.csv' csv header
COPY 2
db=> DROP VIEW v1;
DROP VIEW

answered Feb 23 '17 at 00:48

Mike T

41,085
18
152
203

1

Do you need to explicitly remove the view if it is temp view? – nyxz Nov 28 '17 at 10:41
2

@nyxz that's correct, you don't need to drop this view. It may only be necessary if you have multiple queries to process with `v1`. – Mike T Nov 28 '17 at 20:44
1

Easiest solution I have found. Thanks! – Steven L. Jan 08 '19 at 15:14
3

what about if you only have read only permission on the database? – Lost Crotchet Sep 16 '21 at 23:45

score 5 · Answer 2 · edited May 15 '22 at 11:48

5

We may use HEREDOC to feed multiline SQL to psql and use

# Putting the SQL using a HEREDOC
tr '\n' ' ' << SQL| \psql mydatabase
\COPY (
  SELECT
    provider_id,
    provider_name,
    ...
) TO './out.tsv' WITH( DELIMITER E'\t', NULL '', )
SQL

Source: https://minhajuddin.com/2017/05/18/how-to-pass-a-multi-line-copy-sql-to-psql/

edited May 15 '22 at 11:48

Greywolf

3
3

answered Feb 27 '20 at 20:20

Saim

2,471
5
30
43

score 1 · Answer 3 · answered Feb 06 '23 at 04:59

You can combine the server side COPY command with the \g psql command to produce a multi-line query to local file:

db=# COPY (
    SELECT department, count(*) AS employees
    FROM emp
    WHERE role = 'dba'
    GROUP BY department
    ORDER BY employees
) TO STDOUT WITH CSV HEADER \g department_dbas.csv
COPY 5

I describe this technique in detial here https://hakibenita.com/postgresql-unknown-features#use-copy-with-multi-line-sql

Use psql's \copy for a multi-line query

3 Answers3

Linked