Redshift removes a backslash from string stored in a varchar field

Question

Hi Good People,  
  
Redshift and Aurora or plain PostgreSQL works a little bit differently in terms of storing backslashes in a varchar field. Redshift removes one backslash in distinction from Aurora which is inserting string as it is. **Is it OK?**  
  
Minimal example:  
On Redshift:

```
 select '\' as test from test_table limit 1;
 ```

returns:

```
\
```

On Aurora or pgsql:

```
select '\' as test from test_table limit 1;
```

returns:

```
\
```

I would like to store JSON in varchar field in RS. Problems start when JSON contains backslash sign to escape JSON's token:

```
{"field":"text""}
```

In result, \ is removed from stored value changing the value that has been requested to be stored, eventually corrupting JSON which cannot be deserialized.

```
CREATE TABLE test_table(
  varchar_field varchar
);

insert into test_table_1 (varchar_field) VALUES ('{"field":"text""}');
select * from test_table;
```

returns:

```
{"field":"text""}
```

which is not proper JSON but a proper JSON were inserted.  
   
For the same example, Aurora returns a proper JSON:

```
{"field":"text""}
```

**Is this desired behavior?**   
**How can I prohibit removing backslashes?**  
**Is somewhere described how storing texts in RS different from other DBs?**  
 I'm inserting data to RS via insert statement, it won't be doable in short term solution to use COPY instead. How can I deal with it? It's highly possible that I'm missing something crucial here but I stuck for a few hours and cannot find a solution by myself :)   
  
**RS Cluster Version:** 1.0.7562  
**RS Driver version:** 1.2.16.1027  
  
Best regards,  
pby  
  
Edited by: pby on May 17, 2019 7:49 AM  
  
Edited by: pby on May 17, 2019 8:03 AM

Accepted Answer

Hi pby,  
  
I suspect the difference in the behavior may be tied to the base PostgreSQL version for each the test cases you demonstrate.   
  
Redshift is a single version derivative of a PostgreSQL v8.0.2 fork as documented here: https://docs.aws.amazon.com/redshift/latest/dg/c_redshift-and-postgres-sql.html   
  
Aurora versions are derivatives of many PostgreSQL versions documented here: https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Updates.20180305.html  
  
PostgreSQL will be whatever version it is.  
  
I suspect the escape handling in your string literals is an artifact of the base PostgreSQL version, i.e. Redshift and PosgreSQL v 8.0.2 will likely do the same thing.  
  
I hope this helps,  
-Kurt

Answer

Hi klarson,  
  
Thank you. You right.  
  
I checked on postgres v8.0.2 and, in fact, '\' is handled differently than with postgres 10.   
  
Best regards,  
pby

Redshift removes a backslash from string stored in a varchar field

相關內容