Обсуждение: [ADMIN] ERROR: invalid byte sequence for encoding "UTF8": 0x00
All; 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 If I use replace then ALL rows are NULL: select id, replace(cname,'',NULL) from ora_fdw_schema.cust_names id | replace ---------+---------------------- 1 | 2 | 3 | 4 | 5 | 6 | 7 | Same thing if I use translate: select id, translate(cname,'',NULL) from ora_fdw_schema.cust_names Help? Thanks in advance -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin