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?
Best Answer
The documentation mentions the relationship between locales and SQL features in Locale Support:
The first item (sort order) is about
LC_COLLATE
and the others seem all to be aboutLC_CTYPE
.LC_COLLATE
LC_COLLATE
affects comparisons between strings. In practice, the most visible effect is the sort order.LC_COLLATE='C'
(orPOSIX
which is a synonym) means that it's the byte order that drives comparisons, whereas a locale in thelanguage_REGION
form means that cultural rules will drive the comparisons.An example with french names, executed from inside an UTF-8 database:
Result:
béatrice
comes beforeboris
, because the accented E compares against O as if it was non-accented. It's a cultural rule.This differs from what happens with a
C
locale:Result:
Now the names with accented E are pushed at the end of the list. The byte representation of
é
in UTF-8 is the hexadecimalC3 A9
and foro
it's6f
.c3
is greater than6f
so under theC
locale,'béatrice' > 'boris'
.It's not just accents. There a more complex rules with hyphenation, punctuation, and weird characters like
œ
. Weird cultural rules are to be expected in every locale.Now if the strings to compare happen to mix different languages, as when having a
firstname
column for people from all other the world, it might be that any particular locale should not dominate, anyway, because different alphabets for different languages have not been designed to be sorted against each other.In this case
C
is a rational choice, and it has the advantage of being faster, because nothing can beat pure byte comparisons.LC_CTYPE
Having
LC_CTYPE
set to 'C' implies that C functions likeisupper(c)
ortolower(c)
give expected results only for characters in the US-ASCII range (that is, up to codepoint 0x7F in Unicode).Because SQL functions like
upper()
,lower()
orinitcap
are implemented in Postgres on top of these libc functions, they're affected by this as soon as there are non US-ASCII characters in strings.Example:
For the
C
locale,é
is treated as an uncategorizable character.Similarly wrong results are also obtained with regular expressions: