Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

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

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...