Postgres：.sql文件中的\copy语法错误

本文介绍了Postgres：.sql文件中的\copy语法错误的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在尝试编写一个脚本，用于将数据从交叉表查询复制到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;