Elixir - Postgres: invalid byte sequence for encoding \"UTF8\

Question

I'm currently working on an elixir project that parses XML from an API and inserts data into postgres using postgrex.

Most inserts work fine, however for the odd insert I get this error. I've seen a lot of other people facing this error, but I'm not to sure how to solve it in Elixir.

23:52:32.402 [error] Process #PID<0.224.0> raised an exception
** (KeyError) key :constraint not found in: %{code: :character_not_in_repertoire, file: "wchar.c", line: "2011", message: "invalid byte sequence for encoding \"UTF8\": 0xe3 0x83 0x22", pg_code: "22021", routine: "report_invalid_encoding", severity: "ERROR"}
    (pipeline_processor) lib/worker.ex:133: PipelineProcessor.Worker.recursive_db_insert/1
    (pipeline_processor) lib/worker.ex:47: PipelineProcessor.Worker.process_article/1
    (pipeline_processor) lib/worker.ex:17: PipelineProcessor.Worker.request_article/0

I'm aware that the error is actually due to accessing an invalid property of the map. However I'm trying to solve the issue that postgrex is giving.

My postgrex insert code:

sql_string = "INSERT INTO articles (title, source, content) VALUES ($1, $2, $3) RETURNING id"
{:ok, pid} = Postgrex.Connection.start_link(Application.get_env(:pipeline_processor, :db_details))
response = Postgrex.Connection.query(
  pid,
  sql_string,
  [article.title, article.source, article.content]
)

Postgrex.Connection.stop(pid)

Is there anyway in Elixir to scrub out invalid bytes so that these inserts can succeed? Or for some way to have postgres handle it?

Thanks

Answer 1

As you already guessed postgres is complaining that you are inserting invalid UTF8 into a text type. I would initially try to fix the bad encodings if you cannot do that you can use a combination of String.codepoints/1 and String.valid_character?/1 to either scrub or escape the invalid bytes.