PostgreSQL COPY FROM issue-CodePudding

DROP table if exists legislators;

CREATE table legislators
(
...
)
;

COPY legislators 
FROM 'C:\data\legislators.csv'
DELIMITER ',' -- It is written in different line from `FROM` clause but it raises ERROR.
CSV HEADER;

I am trying to import CSV file to PostgreSQL on HeidiSQL v11. When I execute a query written in muliple lines as above, it raises an error:

ERROR: syntax error at or near "CSV" LINE 1: CSV HEADER;

However, I found that if I write a FROM clause and DELIMITER ',' in a single line together as below, it works well.

COPY legislators 
FROM 'C:\data\legislators.csv' DELIMITER ',' -- These FROM and DELIMITER should be the same line to work 
CSV HEADER;

I know SQL basically ignores whitespace, but I am confused why this happens.

It would be very appreciate someone help me. Thanks.

CodePudding user response：

That's just because HeidiSQL is not very smart about parsing PostgreSQL lines and gets confused. It executed the statement as two statements, which causes the error.

Use a different client with PostgreSQL.