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.

Answer 1

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 

Answer 2

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

# Putting the SQL using a HEREDOC cat <<SQL | tr '\n' ' ' | \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/