Postgres：.sql文件中的\copy语法错误 [英] Postgres: \copy syntax error in .sql file

问题描述

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

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 -f option, I get a syntax error.

以下是我正在查看的示例（来自这个很好的答案）：

Here's an example of what I'm looking at (from this great 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.

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

What is causing the syntax error and how can I copy this table to a csv file using script file?

推荐答案

与此答案一样，创建多行 VIEW 和单行 \copy 命令，例如：

As with this answer, create a multi-line VIEW with a single-line \copy command, e.g.:

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;

这篇关于Postgres：.sql文件中的\copy语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！