PostgreSQL PSQL Commands – Client-Side Commands and New Lines (\COPY)

clientcopypostgresqlpsql

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.

Best Answer

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
Related Question