PostgreSQL COPY FROM issue

Question

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.

You could try using the newer style of specifying the options WITH as : ...FROM 'C:\data\legislators.csv' WITH (FORMAT csv, HEADER, DELIMITER ','). FYI, DELIMITER ',' is redundant for CSV as that is the default delimiter. — Adrian Klaver
– Adrian Klaver, Commented Mar 1, 2022 at 16:44

Laurenz Albe · Accepted Answer · 2022-03-01 14:25:47Z

1

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.

answered Mar 1, 2022 at 14:25

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Park Over a year ago

Thank you for the answer. I did not expect that HeidiSQL has the drawback.

Collectives™ on Stack Overflow

PostgreSQL COPY FROM issue

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related