5

The error message is the same with super user using COPY. The files are located on the same server as the postgres server. Saw many similar posting but not a single one answer my question.

\copy table_name from '/path/to/csv/file.csv'
with format csv, header true ;

ERROR: syntax error at or near "format"

Tied with parenthesis or not. Read the manual at version 10 for postgres carefully. Not helping. I must be missing some minor point, please point out.

1
  • 1
    Welcome to Database Administrators This is a really good question in that it confuses a lot of people. Please stick around. You seem to have a follow-up question which is perfectly fine. It's better to keep questions atomic though, can you ask the other question again as a separate submission here? Oct 24, 2017 at 4:39

1 Answer 1

6

psql "meta-commands"

Commands in psql that start with back-slash (\) are called "meta-commands" and they don't follow the usual semicolon rules but are instead terminated by a newline. A small excerpt from man psql

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.

Much more said on this in the man pages. Yes, it's weird and annoying. It's because historically psql commands were a sort of metacommand you'd want to run without interrupting any query you were building, like \d to see columns of a table. But for pseudo-statements like \copy it gets confusing.

What's happening here

In your case, what's happening is that psql runs the first line, then sends the second to the postgres server, which recognises WITH as a valid CTE statement token then gets confused by FORMAT.

What you want is,

\copy table_name from '/path/to/csv/file.csv' with format csv, header true
8
  • 1
    Sometimes I wish PostgreSQL valued sanity more than backwards compatibility. =/ Oct 24, 2017 at 4:01
  • 1
    Me too. Very much me too. But that said, whenever we do break BC, all hell breaks loose. bytea_output was a nightmare. Oct 24, 2017 at 4:02
  • 1
    The title is good. I have used postgrs copy command for at least 10 thousand times in the past, but have not used it for the last few years. This one got me. I was trying to figure out why because there was another issue with my header. The passer just started parsing the value without consulting the with header true setting.
    – Kemin Zhou
    Oct 24, 2017 at 4:26
  • 1
    A lot of people are confused by COPY vs \COPY too. I think it's a real shame \copy wasn't \CLIENTCOPY or something Oct 24, 2017 at 4:26
  • 1
    Very good education for me, everything has to be in one line.
    – Kemin Zhou
    Oct 24, 2017 at 4:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.