Postgres: \copy syntax error in .sql file

Question

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.

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

You're able to run that `\copy` as-is from the `psql` command line? — Politank-Z, Apr 14 '15 at 16:42
For giggles, try taking all of the newlines out of the `\copy` command in your file and see what happens. — Politank-Z, Apr 14 '15 at 16:50
Ok, so it appears to be a problem with the newlines. It works if there are no newline characters but any newlines create a syntax error at the closing parenthesis. — David Kelley, Apr 14 '15 at 16:56

score 20 · Answer 1 · edited Jan 06 '17 at 16:17

psql thinks 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.

Relevant excerpts from psql manpage with some emphasis added:

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.

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.

As told in the comments, the fix would be to cram the whole meta-command into a single line.

Mike T · Accepted Answer · 2019-03-11T19:46:56.053

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;

score 3 · Answer 3 · answered May 18 '17 at 09:23

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.

# 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

score 1 · Answer 4 · edited Jun 20 '20 at 09:12

According to the psql documentation:

-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.

This would be one of those cases where the -f option 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.

Redirecting the file to stdin produces the same error except that the error is reported to have occurred on line 1 instead of line 7. — David Kelley, Apr 14 '15 at 18:36

Postgres: \copy syntax error in .sql file

4 Answers4

Linked