postgresql - Postgres :\copy syntax error in . sql文件

Question

我正在尝试编写一个脚本，将数据从交叉表查询复制到 Postgres 8.4 中的 .csv 文件。我能够在 psql 命令行中运行该命令，但是当我将该命令放入文件中并使用 -f 选项运行它时，出现语法错误。

这是我正在查看的示例(来自 this 很好的答案):

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

然后我运行它并得到以下语法错误:

$ 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
        ^

一个类似的练习只做一个没有交叉表的简单查询就可以正常工作。

是什么导致语法错误？如何使用脚本文件将此表复制到 csv 文件？

Answer 1

psql 认为您的第一个命令只是 \copy ( 和下面的行来自另一个不相关的语句。元命令不会分布在多行上，因为换行符是他们的终结者。

相关摘录自psql manpage添加了一些重点:

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.

所以第一个错误是 \copy ( 失败，然后下面的行被解释为一个独立的 SELECT，它看起来很好，直到第 7 行有一个虚假的右括号。

如评论中所述，解决方法是将整个元命令塞进一行。