Amazon Redshift COPY command fails when VARCHAR field ends with backslash

Question

In my csv I have data like:

123|456|value 1\

My copy command is:

copy "table_name" ("a_id", "b_id", "b_name") from s3:bucket/path.csv delimiter '|' escape;

The problem I'm having is that, because the b_name value (value 1\) ends in a backslash, I'm getting an Extra column(s) found error.

I believe it's because I'm using the escape parameter to escape backslashes and redshift is expecting something to escape after the trailing backslash.

Is there an elegant way to deal with this? I could append another backslash any time I encounter a string that ends in backslash. But that leads to problems if strings end in two or more backslashes, I have to escape every one.

Answer 1

The documentation for ESCAPE says:

When this parameter is specified, the backslash character () in input data is treated as an escape character. The character that immediately follows the backslash character is loaded into the table as part of the current column value, even if it is a character that normally serves a special purpose. For example, you can use this parameter to escape the delimiter character, a quotation mark, an embedded newline character, or the escape character itself when any of these characters is a legitimate part of a column value.

Therefore, I suggest you remove ESCAPE from the command, which will load it as part of the column contents but will probably avoid the problem you are experiencing.

You could always run some commands on the data once it is in Redshift, to remove the trailing slash.