使用psql的\copy进行多行查询 [英] Use psql's \copy for a multi-line query

问题描述

这是此答案中针对 将PostgreSQL的PL / pgSQL输出保存到CSV文件中。

我需要编写客户端CSV使用psql的 \copy 命令。一个班轮行得通：

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

$的末尾解析错误b
$ b

是否可以在跨多行的查询中使用 \copy ？我在Windows上使用psql。

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

推荐答案

我目前可用的解决方案是创建一个临时视图，可以在多行中声明它，然后在 \copy 命令，非常适合一行。


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

这篇关于使用psql的\copy进行多行查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！