More like the ongoing pain.
I had to write the following just this year because SQL Server still defaults to using CP 1252 for text. The culprit? One of those damned stylized quotes that Office loves to insert for you. The code:
def _wrap_str(value: str):
try:
return SqlVarChar(
value.encode("cp1252")
)
except UnicodeEncodeError:
logging.getLogger("bulk copy").exception(f"value causing error: {value}")
raiseIn the Linux/Postgres world, everything is UTF-8. Which is the default for all internet protocols. Do that and the pain is gone.
Of course Windows doesn't do that...
Windows, the OS, is UTF-16 (or UCS-2 - I forget the details between the two), SQL Server has, for historical reasons, defaulted to CP1252, probably for compatibility with Office components.
But, it's not really a Windows problem, per se, because you have to deal with this issue if you deal with data originated from numerous Windows apps, even if on Linux. Yeah, you can insert a byte order mark (BOM) to indicate UTF-8, but most tools expecting UTF-8 actually dont check for the BOM and blow up in interesting ways if present. Ive seen this far too many times. Enough that anytime I see an encoding error from the likes of Python or Ruby, its an instant recognition (I do a lot ETL work from a number of vendors, so I see a lot of different files "types").
https://wiki.postgresql.org/wiki/Abbreviated_keys_glibc_issu...
Since Postgres 10 they use ICU instead of relying on the C library string routines to give more control.
https://www.2ndquadrant.com/en/blog/icu-support-postgresql-1...
Save a file in Notepad. Open in vi. See that it is different. Find data in the database, no clue the weird characters were originally supposed to be. And so on and so forth.
I once wrote a reasonable program and sent it as a bug report to the maintainer of the Perl module DBD::File. He sent it as a bug report to BerkeleyDB. They said they never thought about it but yes, that would be silent data corruption with no way to recover. The program? Maintain an address book in a BTree sorted in the current locale. Enter names. Change locale and insert something else. Voila! Lost data with no way to recover!