Postgres foreign data wrapper encoding issue

Question

I'm trying to migrate Oracle data with 'we8mswin1252' encoding to my postgres database which has 'UTF8' encoding.

I'm using foreign data wrapper.

I'm getting

invalid byte sequence error

What should i do?

Welcome to StackOverflow! Please read and follow the posting guidelines in the help documentation regarding on topic and how to ask. StackOverflow is not a coding or tutorial service. — Ted Nyberg
– Ted Nyberg, Commented Sep 14, 2016 at 15:14

Laurenz Albe · Accepted Answer · 2016-09-13 07:12:47Z

2

Such errors can be caused by two things:

There may be zero bytes in your Oracle strings. That is allowed in Oracle (even though it is problematic), but forbidden in PostgreSQL.
It is easy to get data corruption in Oracle, because it is pretty sloppy with encoding checks and allows you to insert arbitrary illegal byte sequences when client encoding and server encoding are the same.

There are two approaches to dealing with this problem:

The correct way: fix the data on the Oracle side. oracle_fdw will support you by telling you which row in the result set caused the error.
The sloppy way: use a PostgreSQL database with database encoding sql_ascii, which will allow you to store anything in a string (except zero bytes).

answered Sep 13, 2016 at 7:12

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user3837299 Over a year ago

Laurenz, thanks for your reply, but can we create a database with sql_ascii encoding separately ? We have set it up in utf8 format. Also, we can't edit oracle data.

Laurenz Albe Over a year ago

Sure: CREATE DATABASE badchars ENCODING sql_ascii LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0; Then you should set the nls_lang option on the foreign data wrapper to either use WE8MSWIN1252 if you want the garbage data unchanged or AS32UTF8 if you want them converted to UTF-8 as good as possible. Zero bytes will still be a problem.

Collectives™ on Stack Overflow

Postgres foreign data wrapper encoding issue

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related