FATAL non whitespace after quoted data

[id-gena]

Hi. Yes, I know about this one 765 but there is a new case which causes this error. I have csv file and one of the columns contains such string:

4321 North "W" St

Also I have such settings

WITH skip header = 1,
     fields terminated by ',',
     drop indexes,
     on error resume next,
     fields optionally enclosed by '"', 
     fields escaped by double-quote;

But I still getting this error. How can I fix it? Thanks

P.S fields not enclosed causes shifting all columns to the right side and I have a situation when varchar data tries to be inserted into integer column.

[dimitri]

I need more information here to be able to help you. Can you prepare a self-contained test case? See https://pgloader.readthedocs.io/en/latest/bugreport.html#test-cases-to-reproduce-bugs for details how to do that.

[mihow]

@id-gena It looks like the quotes in your case are actually part of the value. They are not special characters enclosing a value, they are not used to escape a special character, and there is no other character escaping the quotes. So in this case I would just use the option fields not enclosed and remove your other options.

WITH skip header = 1,
     fields terminated by ',',
     drop indexes,
     fields not enclosed,
     on error resume next,

I have similar data that only used tabs as delimitators and there are quotes in the field values. This works great for that case.