使用psql的\copy实现多行查询

Question

这是一个跟进问题，来源于“将PL/pgSQL输出从PostgreSQL保存到CSV文件”的问题的答案。

我需要使用psql的\copy命令编写客户端CSV文件。一行代码就能搞定:

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

然而，我有一些跨越多行的长查询。我不需要显示查询，因为我似乎无法将其扩展到超过一行而没有解析错误：

但是，我有跨越多行的长查询。没有解析错误的情况下，我并不需要展示这个查询：

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

在Windows上使用psql时，是否可以使用跨越多行的查询与\copy一起使用？

- Mike T

---

你可能需要转义换行符？ - Falmarri

@Falmarri 我在我的问题中列出了一些常见的转义尝试，但是从文档中可以看到“psql的...反斜杠转义不适用于\ copy”，这使得事情变得困难。 - Mike T

我已在此发布了答案：https://dev59.com/e10b5IYBdhLWcg3wEdsx#44043716 这是一种获取多行的迂回方法。 - Khaja Minhajuddin

Answer 1

我现在使用的工作解决方案是创建临时视图，可以分多行声明，然后在\copy命令中从中选择，这适合放在一行上。

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

- Mike T

---

如果视图是临时视图，您需要显式删除它吗？ - nyxz

@nyxz，你说得对，你不需要删除这个视图。只有在使用v1处理多个查询时才可能需要。 - Mike T

我找到的最简单的解决方案。谢谢！ - Steven L.

如果您只拥有数据库的只读权限怎么办？ - Lost Crotchet

Answer 2

我们可以使用HEREDOC来传递多行SQL到psql, 并使用它。

# 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

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

本文介绍如何在 psql 中传递多行 SQL 命令。您可以使用以下命令来实现此目的：

psql -c "$(cat <<EOF
YOUR SQL QUERIES HERE
EOF
)"

将“YOUR SQL QUERIES HERE”替换为您要运行的 SQL 命令。请注意，在括号中使用双引号而不是单引号非常重要。

- Saim

Answer 3

你可以将服务器端的COPY命令与\g psql命令结合使用，生成多行查询到本地文件：

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

我在这里详细描述了这种技术 https://hakibenita.com/postgresql-unknown-features#use-copy-with-multi-line-sql

- haki

---

优雅！在我看来，应该超越其他答案。 - undefined