Unicode + LC_COLLATE

Tom Lane said:

C locale basically means "sort by the byte sequence values". It'll do
something self-consistent, but maybe not what you'd like for UTF8
characters.

OK, that explains that. I guess I will need to try it out to see what the
effect is on extended character sets.

Our database is UNICODE with LC_COLLATE=en_US.iso885915.

Does that sort rationally at all? I should think you'd need to specify
an LC_COLLATE setting that's designed for UTF8 encoding, not 8859-15.

Er..., actually the LC_COLLATE for the DB in question is C - I was looking
at the wrong database (wrong telnet session)! So your comments above apply
in this case.

If you only ever store characters that are in 7-bit ASCII then none of
this will affect you, and you can get away with broken combinations of
encoding and locale. But if you'd like to sort characters outside the
minimal ASCII set then you need to get it right ...

Tom, thanks for the answers above.

I guess if I have some time I should build some different DBs with
different combinations of encoding and collations and summarise my
findings using different types of data and sort/search commands, in case
anyone else has the same level of confusion that I do...

John Sidney-Woollett

"John Sidney-Woollett" <johnsw@wardbrook.com> writes:

Does anyone know what the effect of --lc-collate=C --encoding=UNICODE will
be for sorts (and indexes?) when a multibyte unicode character is
encountered?

C locale basically means "sort by the byte sequence values". It'll do
something self-consistent, but maybe not what you'd like for UTF8
characters.

Our database is UNICODE with LC_COLLATE=en_US.iso885915.

Does that sort rationally at all? I should think you'd need to specify
an LC_COLLATE setting that's designed for UTF8 encoding, not 8859-15.

If you only ever store characters that are in 7-bit ASCII then none of
this will affect you, and you can get away with broken combinations of
encoding and locale. But if you'd like to sort characters outside the
minimal ASCII set then you need to get it right ...

regards, tom lane

Am Donnerstag, 22. April 2004 13:17 schrieb John Sidney-Woollett:

Does anyone know what the effect of --lc-collate=C --encoding=UNICODE will
be for sorts (and indexes?) when a multibyte unicode character is
encountered?

You get your strings sorted in binary order of the UTF-8 encoding, which is
probably not very interesting, but it's possible.

Is it also true that if LC_COLLATE != 'C' that indexes cannot be used for
LIKE comparisons (and is this also true for en_US.iso885915)?

No, see <http://www.postgresql.org/docs/7.4/static/indexes-opclass.html&gt;.

Our database is UNICODE with LC_COLLATE=en_US.iso885915. Does anyone know
what the effect of someone storing a cyrillic/chinese or korean character
is?

This setup will result in UTF-8 characters being sorted by the system thinking
they are actually ISO-8859-15 characters. So the result will be random at
best.

(We are using JDBC with a webapp so all the unicode concerns are
handled transparently, apparantly). When the data is extracted from the DB
will it render correctly in the browser provided we send all responses
encoded in UTF-8?

If your database is in UNICODE and you're using JDBC then you should be all
set as far as PostgreSQL is concerned. Of course, your HTML pages need to
declare the encoding correctly as well.

Peter Eisentraut said:

Am Donnerstag, 22. April 2004 13:17 schrieb John Sidney-Woollett:
You get your strings sorted in binary order of the UTF-8 encoding, which
is probably not very interesting, but it's possible.

Agreed.

Is it also true that if LC_COLLATE != 'C' that indexes cannot be used
for LIKE comparisons (and is this also true for en_US.iso885915)?

No, see <http://www.postgresql.org/docs/7.4/static/indexes-opclass.html&gt;.

I wish I understood what this page actually was trying to say.

Is it saying that varchar_pattern_ops sorts according to the 'C' locale
regardless of LC_COLLATE, and that varchar_ops sorts according to the
current value of LC_COLLATE?

This setup will result in UTF-8 characters being sorted by the system
thinking
they are actually ISO-8859-15 characters. So the result will be random at
best.

Actually the LC_COLLATE is currently 'C' not as I reported ISO-8859-1.

What would be a correct LC_COLLATE value for my database if we want to
primarily service ISO-8859-1, but allow for
cyrillic/chinese/japanese/korean characters too and have them sorting and
indexing correctly? We are building a multilanguage website...

ls /usr/share/locale produces:
ca de en@boldquot en_SE fi hr ko no sk zh_TW
cs el en_GB en_US fr it locale.alias pl sv
da en en@quot es gl ja nl pt_BR tr

Thanks for anymore info.

John Sidney-Woollett

C locale basically means "sort by the byte sequence values". It'll do
something self-consistent, but maybe not what you'd like for UTF8
characters.

Does that sort rationally at all? I should think you'd need to specify
an LC_COLLATE setting that's designed for UTF8 encoding, not 8859-15.

If you only ever store characters that are in 7-bit ASCII then none of
this will affect you, and you can get away with broken combinations of
encoding and locale. But if you'd like to sort characters outside the
minimal ASCII set then you need to get it right ...

But if you use anything other than C, you can't use indexes in Like-clauses,
right?

Would lc-collate=C be bad in combination with UNICODE encoding? What
lc-collate setting would you recommend for UNICODE encoding which will
provide good sorting for all (most) common languages? (dutch, english,
french, german)

Alexander Priem

John,

I guess if I have some time I should build some different DBs with
different combinations of encoding and collations and summarise my
findings using different types of data and sort/search commands, in case
anyone else has the same level of confusion that I do...

that'd be excellent. Be sure to offer the writeup for
inclusion into the techdocs site.

Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346

Priem, Alexander said:

Would lc-collate=C be bad in combination with UNICODE encoding? What
lc-collate setting would you recommend for UNICODE encoding which will
provide good sorting for all (most) common languages? (dutch, english,
french, german)

It seems that LC_COLLATE=C is not a good idea when using UTF-8...

On my db server, /etc/sysconfig/i18n contains
LANG="en_GB.UTF-8"
SUPPORTED="en_GB.UTF-8:en_GB:en:en_US.UTF-8:en_US:en"
SYSFONT="latarcyrheb-sun16"

and locale -a produces

C
[..snip..]
en_GB
en_GB.iso885915
en_GB.utf8
[..snip..]
en_US
en_US.iso885915
en_US.utf8
[..snip..]
POSIX

and locale produces

locale
LANG=en_GB.UTF-8
LC_CTYPE="en_GB.UTF-8"
LC_NUMERIC="en_GB.UTF-8"
LC_TIME="en_GB.UTF-8"
LC_COLLATE="en_GB.UTF-8"
LC_MONETARY="en_GB.UTF-8"
LC_MESSAGES="en_GB.UTF-8"
LC_PAPER="en_GB.UTF-8"
LC_NAME="en_GB.UTF-8"
LC_ADDRESS="en_GB.UTF-8"
LC_TELEPHONE="en_GB.UTF-8"
LC_MEASUREMENT="en_GB.UTF-8"
LC_IDENTIFICATION="en_GB.UTF-8"
LC_ALL=

QUESTION: Because I want UNICODE encoding/support in postgres, does that
mean that when I init my db I should specify (for en_GB support)

initdb -E UNICODE <-- (NO LOCALE, default to en_GB)

or

initdb --locale=en_GB -E UNICODE

or should I use

initdb --locale=en_GB.utf8 -E UNICODE

Basically I want the database to be able to support all character
encodings, and to sort according to the en_GB locale.

The monetary and date formats determined by the locale are irrelevant for
us, as we return correctly localised versions of this data from our web
app (which is connected to postgres) based on the user's selected language
for their current web browser session.

Thanks to anyone who can answer the initdb question above, hopefully the
answer will help others too.

John Sidney-Woollett

Am Donnerstag, 22. April 2004 16:37 schrieb Priem, Alexander:

But if you use anything other than C, you can't use indexes in
Like-clauses, right?

No, see <http://www.postgresql.org/docs/7.4/static/indexes-opclass.html&gt;.

Would lc-collate=C be bad in combination with UNICODE encoding? What
lc-collate setting would you recommend for UNICODE encoding which will
provide good sorting for all (most) common languages? (dutch, english,
french, german)

That is currently not supported at all by PostgreSQL. You would need to use a
single-byte encoding.