pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PropAAS DBA <dba@propaas.com>
To: pgsql-admin <pgsql-admin@postgresql.org>
Subject: ERROR: invalid byte sequence for encoding "UTF8": 0x00
Date: Mon, 6 Nov 2017 18:10:14 -0700
Message-ID: <de752e01-f36c-821e-9181-cfba78c0fbc8@propaas.com> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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





-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



view thread (6+ messages)  latest in thread

Message-ID: <de752e01-f36c-821e-9181-cfba78c0fbc8@propaas.com>
Permalink:  ../de752e01-f36c-821e-9181-cfba78c0fbc8@propaas.com/
Also on:    postgresql.org/message-id/de752e01-f36c-821e-9181-cfba78c0fbc8@propaas.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: dba@propaas.com
  Subject: Re: ERROR: invalid byte sequence for encoding "UTF8": 0x00
  In-Reply-To: <de752e01-f36c-821e-9181-cfba78c0fbc8@propaas.com>
  References: <de752e01-f36c-821e-9181-cfba78c0fbc8@propaas.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox