unterminated CSV quoted field in Postgres

Question

I'm trying to insert some data into my table using the copy command :

copy otype_cstore from '/tmp/otype_fdw.csv' delimiter ';' quote '"' csv;

And I have this answer :

ERROR: unterminated CSV quoted field

There is the line in my CSV file where I have the problem :

533696;PoG;-251658240;from id GSW C";

This is the only line a double quote and I can't remove it, so do you have some advice for me ?

Thank you in advance

Answer 1

If you have lines like this in your csv:

533696;PoG;-251658240;from id GSW C";

this actually means/shows the fields are not quoted, which is still perfectly valid csv as long as there are no separators inside the fields.

In this case the parser should be told the fields are not quoted.

So, instead of using quote '"' (which is actually telling the parser the fields are quoted and why you get the error), you should use something like quote 'none', or leave the quote parameter out (I don't know Postgres, so I can't give you the exact option to do this).

Ok, I did a quick lookup of the parameters. It looks like there is not really an option to turn quoting off. The only option left would be to provide a quote character that is never used in the data.

quote E'\b' (backspace) seems to work ok.

Answer 2

Bit late to reply, but I was facing same issue and found that for double quote we need to add one more double quote to escape with along with prefix and suffix double quote to that special characater. for your case it would be

input data is : from id GSW C"

change data to : from id GSW C""""

note there are 4 consecutive double quotes. first and last double quote is prefix and postfix as per documentation. middle 2 double quotes is data with one escape double quote. Hope this helps for readers with similar issue going forward.

So for every double quote in data it needs to be escaped with one escape character (default double quote). This is as per documentation.