For several years now, I've been hunting this extremely quick-footed little bug which knows every hiding place in my metaphorical coding house. He lurks in the walls, sometimes takes months to show himself, and BAM! He's back, without forewarning. And the hunt continues. And then he runs into a hole in the wall and I can't get him out again no matter what. And so the wait begins...
To make a long story short: PHP and PostgreSQL simply do not have a fully consistent idea of what constitutes a valid UTF-8 string. I can now say this for 100% sure.
To avoid the errors that inevitably are logged when I try to INSERT data which PHP claims is valid UTF-8 but PostgreSQL thinks is not (regardless of who is actually right), I must make some kind of pre-query whose only job is to check if the string is valid UTF-8 according to PostgreSQL. Is this possible somehow?
I'm thinking of something like this:
This would then return a true/false so that I can know for sure if I can go ahead and execute the real, INSERT query, or skip it.