postgresql Postgres：.sql 文件中的 \copy 语法错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/29632700/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres: \copy syntax error in .sql file
提问by David Kelley
I'm trying to write a script that copies data from a crosstab query to a .csv file in Postgres 8.4. I am able to run the command in the psql command line but when I put the command in a file and run it using the -foption, I get a syntax error.
我正在尝试编写一个脚本，将数据从交叉表查询复制到 Postgres 8.4 中的 .csv 文件。我能够在 psql 命令行中运行该命令，但是当我将该命令放入文件并使用该-f选项运行它时，出现语法错误。
Here's an example of what I'm looking at (from thisgreat answer):
这是我正在查看的示例（来自这个很棒的答案）：
CREATE TEMP TABLE t (
section text
,status text
,ct integer
);
INSERT INTO t VALUES
('A', 'Active', 1), ('A', 'Inactive', 2)
,('B', 'Active', 4), ('B', 'Inactive', 5)
, ('C', 'Inactive', 7);
\copy (
SELECT * FROM crosstab(
'SELECT section, status, ct
FROM t
ORDER BY 1,2'
,$$VALUES ('Active'::text), ('Inactive')$$)
AS ct ("Section" text, "Active" int, "Inactive" int)
) TO 'test.csv' HEADER CSV
I then run this and get the following syntax error:
然后我运行它并得到以下语法错误：
$ psql [system specific] -f copy_test.sql
CREATE TABLE
INSERT 0 5
psql:copy_test.sql:12: \copy: parse error at end of line
psql:copy_test.sql:19: ERROR: syntax error at or near ")"
LINE 7: ) TO 'test.csv' HEADER CSV
^
A similar exercise doing just a simple query without crosstab works without incident.
一个类似的练习只做一个没有交叉表的简单查询，没有任何意外。
What is causing the syntax error and how can I copy this table to a csv file using script file?
导致语法错误的原因是什么，如何使用脚本文件将此表复制到 csv 文件？
采纳答案by Mike T
As with this answer, create a multi-line VIEWwith a single-line \copycommand, e.g.:
与此答案一样，VIEW使用单行\copy命令创建多行，例如：
CREATE TEMP TABLE t (
section text
,status text
,ct integer
);
INSERT INTO t VALUES
('A', 'Active', 1), ('A', 'Inactive', 2)
,('B', 'Active', 4), ('B', 'Inactive', 5)
, ('C', 'Inactive', 7);
CREATE TEMP VIEW v1 AS
SELECT * FROM crosstab(
'SELECT section, status, ct
FROM t
ORDER BY 1,2'
,$$VALUES ('Active'::text), ('Inactive')$$)
AS ct ("Section" text, "Active" int, "Inactive" int);
\copy (SELECT * FROM v1) TO 'test.csv' HEADER CSV
-- optional
DROP VIEW v1;
回答by Daniel Vérité
psqlthinks your first command is just \copy (and the lines below that are from another unrelated statement. Meta-commands aren't spread on multiple lines, because newline is is a terminator for them.
psql认为你的第一个命令只是\copy (，下面的行来自另一个不相关的语句。元命令不会分布在多行上，因为换行符是它们的终止符。
Relevant excerpts from psql manpagewith some emphasis added:
psql 联机帮助页的相关摘录，并添加了一些重点：
Meta-Commands
Anything you enter in psql that begins with an unquoted backslash is a psql meta-command that is processed by psql itself. These commands make psql more useful for administration or scripting. Meta-commands are often called slash or backslash commands.
....
....(skipped)Parsing for arguments stops at the end of the line, or when another unquoted backslash is found. An unquoted backslash is taken as the beginning of a new meta-command. The special sequence \\ (two backslashes) marks the end of arguments and continues parsing SQL commands, if any. That way SQL and psql commands can be freely mixed on a line. But in any case, the arguments of a meta-command cannot continue beyond the end of the line.
元命令
您在 psql 中输入的任何以不带引号的反斜杠开头的内容都是由 psql 本身处理的 psql 元命令。这些命令使 psql 对于管理或脚本编写更有用。元命令通常称为斜杠或反斜杠命令。
....
....（跳过）参数解析在行尾停止，或者在找到另一个未加引号的反斜杠时停止。未加引号的反斜杠被视为新元命令的开头。特殊序列 \\（两个反斜杠）标记参数的结束并继续解析 SQL 命令（如果有）。这样 SQL 和 psql 命令可以在一行中自由混合。但无论如何，元命令的参数不能继续超出行尾。
So the first error is that \copy (failing, then the lines below are interpreted as an independent SELECT which looks fine until line 7 when there is a spurious closing parenthesis.
所以第一个错误是\copy (失败，然后下面的行被解释为一个独立的 SELECT ，它看起来很好，直到第 7 行，当有一个虚假的右括号时。
As told in the comments, the fix would be to cram the whole meta-command into a single line.
正如评论中所说，解决方法是将整个元命令塞进一行。
回答by Politank-Z
According to the psqldocumentation:
根据psql文档：
-f filename
--file filename
Use the file filename as the source of commands instead of reading commands interactively. After the file is processed, psql terminates. This is in many ways equivalent to the internal command \i.
If filename is - (hyphen), then standard input is read.
Using this option is subtly different from writing psql < filename. In general, both will do what you expect, but using -f enables some nice features such as error messages with line numbers. There is also a slight chance that using this option will reduce the start-up overhead. On the other hand, the variant using the shell's input redirection is (in theory) guaranteed to yield exactly the same output that you would have gotten had you entered everything by hand.
-f 文件名
--file 文件名
使用文件文件名作为命令的来源，而不是交互地读取命令。处理完文件后，psql 终止。这在许多方面等同于内部命令 \i。
如果文件名是 -（连字符），则读取标准输入。
使用这个选项与编写 psql < filename 有微妙的不同。一般来说，两者都可以满足您的期望，但是使用 -f 可以启用一些不错的功能，例如带有行号的错误消息。使用此选项也有可能减少启动开销。另一方面，使用 shell 输入重定向的变体（理论上）保证产生与手动输入所有内容时完全相同的输出。
This would be one of those cases where the -foption treats your input differently from the command line. Removing your newlines worked, redirecting the original file to psql's stdin would likely have worked as well.
这将是-f选项以不同于命令行的方式处理您的输入的情况之一。删除换行符有效，将原始文件重定向到psql's stdin 也可能有效。
回答by Khaja Minhajuddin
The answers listed here explain the reasoning quite clearly. Here is a small hack that allows you to have your sql contain multiple lines and work with psql.
此处列出的答案非常清楚地解释了推理。这是一个小技巧，它允许您让 sql 包含多行并使用 psql。
# Using a file
psql -f <(tr -d '\n' < ~/s/test.sql )
# or
psql < <(tr -d '\n' < ~/s/test.sql )
# Putting the SQL using a HEREDOC
cat <<SQL | tr -d '\n' | \psql mydatabase
\COPY (
SELECT
provider_id,
provider_name,
...
) TO './out.tsv' WITH( DELIMITER E'\t', NULL '', )
SQL
