Re: [ADMIN] ERROR: invalid byte sequence for encoding "UTF8": 0x00

Поиск
Список
Период
Сортировка
От Laurenz Albe
Тема Re: [ADMIN] ERROR: invalid byte sequence for encoding "UTF8": 0x00
Дата
Msg-id 1510040474.2845.41.camel@cybertec.at
обсуждение исходный текст
Ответ на [ADMIN] ERROR: invalid byte sequence for encoding "UTF8": 0x00  (PropAAS DBA <dba@propaas.com>)
Список 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

В списке pgsql-admin по дате отправления:

Предыдущее
От: Laurenz Albe
Дата:
Сообщение: Re: [ADMIN] Standby wal issue
Следующее
От: Yuri Martsinovsky
Дата:
Сообщение: [ADMIN] Running Windows binaries from non-English folder