Hackers,
I’ve discovered something a bit disturbing at $work. We’re migrating (slowly) from Oracle to PostgreSQL, and in some
casesare using oracle_fdw to copy data over. Alas, there are a fair number of text values in the Oracle database that,
althoughthe database is UTF-8, are actually something else (CP1252 or Latin1). When we copy from an oracle_fdw foreign
tableinto a PostgreSQL table, PostgreSQL does not complain, but ends up storing the mis-encoded strings, even though
thedatabase is UTF-8.
I assume that this is because the foreign table, as a table, is assumed by the system to have valid data, and therefor
additionalcharacter encoding validation is skipped, yes?
If so, I’m wondering if it might be possible to add some sort of option to the CREATE FOREIGN TABLE statement to the
effectthat certain values should not be trusted to be in the encoding they say they are.
At any rate, I’m spending some quality time re-encoding bogus values I never expected to see in our systems. :-(
Thanks,
David