Is there an equivalent to Redshift's ACCEPTINVCHARS option for an INSERT INTO statement?

Question

There are many online articles that talk about solving the following error:

String contains invalid or unsupported UTF-8 codepoints. Bad UTF-8 hex sequence: a4 (error 3)

Unfortunately they talk about solving this issue for the COPY command, however I get the same issue with an INSERT INTO.

The solution for the COPY command talks about using the ACCEPTINVCHARS option, however there doesn't seem to be an equivalent for the INSERT INTO statement.

If baffles me that there is no real concept of NVARCHAR in Redshift which would ultimately solve this problem, so I want to know if anyone else has come up with a good solution for dealing with inserting invalid characters using INSERT INTO.

Yes its basically an `INSERT INTO table VALUES (id, 'name', 'type');` — Adel Helal, Jan 24 '18 at 09:23
i suggest you remove the invalid characters at the point you generate those commands. Also it is very slow inserting this way, do you do many of these? if so you should examine the alternatives (copy). — Jon Scott, Jan 24 '18 at 09:28

score 0 · Accepted Answer · answered Feb 11 '18 at 13:22

There seems to be no such support for something similar to ACCEPTINVCHARS in an INSERT INTO statement. The general practice of getting data into Redshift is via bulk insert anyway which is precisely where the COPY command is relevant. Redshift doesn't do well with small inserts.

Lesson learnt is avoid inserts altogether. Go with the COPY command.

Is there an equivalent to Redshift's ACCEPTINVCHARS option for an INSERT INTO statement?

1 Answers1