ERROR: invalid byte sequence for encoding "UTF8": 0x00

From: PropAAS DBA <dba(at)propaas(dot)com>
To: pgsql-admin <pgsql-admin(at)postgresql(dot)org>
Subject: ERROR: invalid byte sequence for encoding "UTF8": 0x00
Date: 2017-11-07 01:10:14
Message-ID: de752e01-f36c-821e-9181-cfba78c0fbc8@propaas.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Mark Kirkwood 2017-11-07 02:49:14 Re: Bad recovery: no pg_xlog/RECOVERYXLOG
Previous Message chris kim 2017-11-06 22:39:53 Re: Standby wal issue