PostgreSQL PSQL client-side commands and new lines (\COPY)

Question

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.

• Evan Carroll: 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?

Answer 1

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

• Evan Carroll: Sometimes I wish PostgreSQL valued sanity more than backwards compatibility. =/
• Craig Ringer: Me too. Very much me too. But that said, whenever we do break BC, all hell breaks loose. bytea_output was a nightmare.
• Kemin Zhou: 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.
• Craig Ringer: A lot of people are confused by COPY vs \COPY too. I think it's a real shame \copy wasn't \CLIENTCOPY or something
• Kemin Zhou: Very good education for me, everything has to be in one line.