Redshift removes a backslash from string stored in a varchar field

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