Postgresql copy CSV format, double quote escape not working-CodePudding

Running into an issue with copying the following data into a DB

1, ab\"c

I receive an unterminated quote error when running the following SQL

copy table_name from sample.tsv CSV DELIMITER ',' QUOTE '"' ESCAPE E'\\'

Based on the postgresql documentation I expect the escape parameter to be used to escape the quotation character but it's not working. Would like to see if there's a solution to this issue without reformatting the data, or changing the quote character.

CodePudding user response：

try this. Because if quote is ", then it will mix with double quote in (ab"c).

copy table_name from 'sample.tsv' (FORMAT CSV, QUOTE '''', DELIMITER ',',ESCAPE E'\\');

CodePudding user response：

It is expecting to find escaped quotes only inside quotes, so the command you show would work for 1,"ab\"c" but not for what you have.

The command that would work for the data you show is:

copy table_name from sample.tsv DELIMITER ','

But it is not likely to work for the rest of your data.