This reminds me a story... At my last job we used Postgres on a Windows server. Whoever initially set up the database pretty much stuck to the defaults. When

This reminds me a story... At my last job we used Postgres on a Windows server. Whoever initially set up the database pretty much stuck to the defaults. Whenever we would read/write text to the db in code we were using functions that basically returned raw data. All of our text was encoding using UTF-8. When using the Windows Postgres Admin tool to run a query that returned text, you'd occasionally see some weird characters (café), sometimes you wouldn't (café). Sometimes the query would fail due to invalid characters. When I looked into it I quickly realized what was wrong - on Windows Postgres defaults to Win1252 encoding (which is a form of extended ascii). Normally when you are writing text to the database Postgres will convert the encoding but since we were writing raw bytes we skipped this part. Win1252 contains some undisplayable characters which occasionally show up in a multi-byte UTF-8 character, explaining the failed queries. However it didn't explain why sometimes we got café and sometimes we got café. It turned out that there were some scripts/apps that were reading file names on Windows and inserting them into the database without converting it to UTF-8. Windows uses UTF-16, although somewhere this was auto-magically being converted to Win1252. This meant that our Win1252 encoded database contained both Win1252 text and UTF-8 text. I ended up doing a text dump of the database and writing a Perl script that would run through it all byte-by-byte and convert all characters above ascii 127 that were not part of valid UTF-8 sequences into UTF-8. This was then used to create a new database correctly encoded as UTF-8. It actually worked (although there were a few snags - we were getting some data from this service that would randomly include the sub character (ctrl-z) occasionally which would cause cat/split to stop running).