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
– Mike T, Commented Feb 23, 2017 at 0:09
I have posted an answer her: stackoverflow.com/a/44043716/24105 which is a roundabout way to get multiple lines. — Khaja Minhajuddin
– Khaja Minhajuddin, Commented May 18, 2017 at 9:25

Mike T · Accepted Answer · 2017-02-23 00:48:48Z

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

Do you need to explicitly remove the view if it is temp view?
@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.
what about if you only have read only permission on the database?

haki · Accepted Answer · 2023-02-06 04:59:42Z

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

You could even skip COPY entirely, just use \gs formatting-option clauses: SELECT ... ORDER BY employees \g (format=csv) department_dbas.csv (header is generated by default here). Source: postgresql.org/docs/current/… and postgresql.org/docs/current/…

Greywolf · Accepted Answer · 2022-05-15 11:48:29Z

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/

Collectives™ on Stack Overflow

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

3 Answers 3

3 Comments

2 Comments

Comments

Linked

Hot Network Questions