PropAAS DBA wrote: > All; That's me :^) > we are doing an oracle to Postgresql conversion, lots and lots of the > oracle columns throw this error: > > ERROR: invalid byte sequence for encoding "UTF8": 0x00 > > CONTEXT: converting column [colname] for foreign table scan of > [tablename] row xxx > > We are using the ora_fdw oracle foreign data wrapper extension to > perform the data conversion, we create the foreign tables via the > "IMPORT FOREIGN SCHEMA" option, then do an insert in [postgres_table] > select * from [ora_fdw_table] > > I've tried a number of fixes but they all seem to replace the data for > ALL rows instead of just the broken ones. > > > For example: > > select id, cname from ora_fdw_schema.cust_names > > ERROR: invalid byte sequence for encoding "UTF8": 0x00 > > CONTEXT: converting column cname for foreign table scan of > ora_fdw_schema.cust_names, row 122 The reason is that there are some zero bytes (ASCII NUL) in the Oracle table. While these are allowed in Oracle, they are not allowed in PostgreSQL because PostgreSQL regards zero bytes as string terminators. That is why oracle_fdw will not allow you to transfer these data to PostgreSQL. > If I use replace then ALL rows are NULL: Any attempt to fix the data on the PostgreSQL side is doomed. The error message is thrown when the data are converted to PostgreSQL string types, which is *before* you can manipulate them in PostgreSQL. You'll have to fix the data on the Oracle side before you migrate the database. Usually, such zero bytes in strings are introduced into the Oracle table by mistake, and no harm is done in removing them. Use a condition like WHERE cname LIKE '%' || CHR(0) || '%' in Oracle to spot the affected rows. Yours, Laurenz Albe -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
