Postgresql backslash escape character not working for commas in COPY FROM

Question

Postgresql backslash escape character not working for escaping a comma delimiter in COPY FROM csv file. I am using Postgresql version 12. I have tried this:

COPY myTable  
    FROM '/tmp/myTest.csv'
    WITH CSV HEADER DELIMITER ',' 
;

and this:

COPY myTable  
    FROM '/tmp/myTest.csv'
    WITH CSV HEADER DELIMITER ',' ESCAPE '\'
;

either one does not work. If I remove all the \, from the input file, it works but I lose all commas that need to be in the data.

Answer 1

That isn't how CSV format works. Only the quote character is escaped, other characters are protected by quoting them, not by escaping them. Per the docs:

ESCAPE: Specifies the character that should appear before a data character that matches the QUOTE value. The default is the same as the QUOTE value (so that the quoting character is doubled if it appears in the data)

If your data is not using quotes to protect special characters, then it isn't CSV. Maybe you should try to load it as FORMAT TEXT, not FORMAT CSV.