Migration From Oracle to PostgreSQL Encoding Errors

Question

I'm working on migrating data from an oracle database to a postgresql database. I running into a problem where I export the data from oracle, using sql developer, and importing it into postgresql. When I try to import the data into postgresql I get this error:

ERROR:  invalid byte sequence for encoding "UTF8": 0xcb 0xcf
CONTEXT:  COPY project, line 646

********** Error **********

ERROR: invalid byte sequence for encoding "UTF8": 0xcb 0xcf
SQL state: 22021
Context: COPY project, line 646

The data in the oracle server looks like this in sql developer:

Ã9ÃýÃ0Ã

When sql developer exports the data to a file, it looks like this:

When I try to set the encoding in sql developer when I export the data I get this loading menu item:

and it doesn't change.

What do I need to do to properly export and import the data from oracle and postgresql?

Answer 1

Sounds like the data you're exporting isn't utf-8 encoded, or is being chopped up / mangled in transit.

This: Ã9ÃýÃ0Ã looks like what happens when you decode utf-8 as iso-8859-15, cp1252, or related 1-byte encodings. But it's not valid utf-8 when demangled. Perhaps you've cut it part way through a string, rather than copying from the beginning of the value?

0xcb 0xcf is indeed nonsense as utf-8. It's a continuation byte without any appropriate context.

Trying other encodings, it's:

• ËÏ in latin-1. Unlikely. Same in iso-8859 3,4,9,10,14,15, and in the default Windows encoding for Western Europe / US, cp1252.
• ËĎ in iso-8859-2
• ЫЯ in iso-8859-5
• ثد in iso-8859-6
• ΛΟ in iso-8859-7
• หฯ in iso-8859-11
• ĖĻ in iso-8859-13

Any of those look like likely candidates, given what you know about the original data?

You need to either export in a known encoding, or determine the encoding of the input reliably.

It's also possible that the data is in fact starting with a utf-8 continuation byte and was supposed to be utf-8, but something has mangled it by cutting utf-8 strings up byte-wise.

If you can post a complete line of exported CSV it might help.