postgresql translator - remove all nulls & non printable characters from text?

[omarbkhan]

Hi - I'm having to wrap my text fields in teiid with  REPLACE(field, '\u0000', '') to remove nulls as postgreql or I will get :

Postgres error on insert - ERROR: invalid byte sequence for encoding “UTF8”: 0x00

I was wondering, perhaps if something could be added to the translator strip all non-printable characters ? I at this point can only remove nulls. I did also try the following pattern - but it didn't work: \p{C}

or maybe there is another way to do it more efficiently than just wrapping various text field with a bunch of replace(s)?

[shawkins]

> Postgres error on insert - ERROR: invalid byte sequence for encoding “UTF8”: 0x00

That's a fundamental limitation of PostgreSQL, which would be expected in this case.

> at this point can only remove nulls. I did also try the following pattern - but it didn't work: \p{C}

Can you elaborate, how did you try that pattern?

Do you mean in the regexp_replace function on Teiid 8.11+?  One consideration there is that regexp_replace won't be pushed down.

> I was wondering, perhaps if something could be added to the translator strip all non-printable characters ?

You could log an enhancement to for that, but at least for postgresql it does support the other ISO control characters.

[omarbkhan]

I haven't tried REGEXP_REPLACE, but will next. I'm not clear on the meaning of pushed down, and I should be. Does it mean it won't be sent as native sql to the source system. I'm not understanding the context ?

Where/how I log an enhancement request? to have this added?

[shawkins]

> Does it mean it won't be sent as native sql to the source system. I'm not understanding the context ?

Yes, currently regexp_replace is only evaluated in the Teiid engine.

> Where/how I log an enhancement request? to have this added?

You should use the project JIRA - Teiid - JBoss Issue Tracker