Hello list
I'm slowly converting all of our databases from Postgres 9 and LATIN1 to
Postgres 12 and UTF8, one by one.
I was wondering if there is a solution for this issue: if a database
that is still latin1 has a postgres_fdw foreign table onto a converted
utf8 database, and somehow a character that is not in latin1 has already
gotten in there, a query might fail with e.g.
PG9=# select * from fdw_table_test where hostname ~* 'moscow-ix-02';
ERROR: character with byte sequence 0xd0 0xad in encoding "UTF8" has no
equivalent in encoding "LATIN1"
-- (0xd0 0xad being CYRILLIC CAPITAL LETTER E: Э)
Can I somehow influence the client:UTF8->server:LATIN1 character set
conversion so that instead of failing, it inserts an invalid codepoint
character, the utf8 hex bytes as string, drops the character or
something like that?
I do agree that the default behavior is correct and in general failing
is a lot better than mutilating or losing data in the conversion.
However in this specific case we don't care all that much about the bits
in possibly foreign scripts, and just having some ? in the string would
be easier to handle than one Cyrillic character in a single row failing
a large import job completely.
Is there any way I can do this on the conversion level* ?
Cheers
Christian
* I get that I could somehow write views too look at the content first
and filter out characters that won't work in LATIN1 before going through
the fdw, but I don't quite know all the tables and columns where this
can become an issue. But if you have a copy/paste ready solution for
that I'll take it as well of course :)