So, I've few Debian servers with PostgreSQL on it. Historically, those servers and PostgreSQL are localized with the Latin 9 charset and back then it was fine. Now we have to handle things like Polish, Greek or Chinese, so changing it become a growing issue.
When I tried to create an UTF8 database, I got the message:
ERROR: encoding UTF8 does not match locale fr_FR Detail: The chosen LC_CTYPE setting requires encoding LATIN9.
Few times I made some research on the subject with my old pal Google, and all I could find was some over-complicated procedures like updating the Debian
LANG, recompile PostgreSQL with the correct charset, editing all the
LC_ system variables and other obscure solutions. So for the time being, we let this issue aside.
Recently, it came back again, the Greeks want the stuff and Latin 9 don't want to. And while I was looking into this issue again, one colleague come at me and said “Nah, it's easy, look.”
He edited nothing, didn't do magic tricks, he just make this SQL query :
CREATE DATABASE my_utf8_db WITH ENCODING='UTF8' OWNER=admin TEMPLATE=template0 LC_COLLATE='C' LC_CTYPE='C' CONNECTION LIMIT=-1 TABLESPACE=pg_default;
And it worked fine.
I actually didn't know about
LC_CTYPE='C' and I was surprised that using this wasn't on the first solutions on Google and even on Stack Overflow. I looked around and I only found a mention on the PostgreSQL documentation.
When LC_CTYPE is C or POSIX, any character set is allowed, but for other settings of LC_CTYPE there is only one character set that will work correctly. Since the LC_CTYPE setting is frozen by initdb, the apparent flexibility to use different encodings in different databases of a cluster is more theoretical than real, except when you select C or POSIX locale (thus disabling any real locale awareness).
So it made me wonder, this is too easy, too perfect, what are the downside? And I've a hard time finding an answer yet. So here I come posting here:
tl;dr: What are the downside of using
LC_CTYPE='C' over a specific localization? Is it bad to do so? What should I expect to break?