Oracle数据库判断utf80x00,migration oracle to postgresql invalid byte sequence for encoding “UTF8”: 0x00...

Im migrating an application from oracle to postgresql. In one of the functions that I already migrated I copy data from a different oracle db (db link in oracle, oracle_fdw extension in postgresql) from a few tables into a local table in my postgresql db. However, I`m getting the next error :

invalid byte sequence for encoding "UTF8": 0x00

I saw that some people had this kind of issue in this forum but they didnt try to copy the data directly from a remote database (they loaded data from a dump or from a csv..).

Some kind of idea what can I do ?

# Answer 1

PostgreSQL does not allow the “zero” character in character strings.

You'll have to sanitize the Oracle data before you can retrieve them from PostgreSQL.

# Answer 2

Now it works with oracle_fdw 2.3.+

Here is code I used

select 'ALTER FOREIGN TABLE "'||table_schema||'"."'||table_name||'" ALTER COLUMN "'||column_name||'" OPTIONS (ADD strip_zeros ''true'');'

from information_schema."columns" c

where table_name ='my_foreign_table_name'

and table_schema ='my_schema_name_where_foreign_table_created'

and udt_name in ('varchar', 'bpchar');