Search code examples
postgresqlheidisql

PostgreSQL COPY FROM issue

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.

Solution

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