Обсуждение: Unicode database on non-unicode operating system

Am Dienstag, 15. Juli 2008 schrieb Morten Barklund:
> My problem is, that the lowercase versions of non-ascii characters are
> broken. Specifically I found, that when lower() is invoked on a text with
> non-ascii characters, the operating system's locale is used for converting
> each octet in the string to lowercase in stead of using the locale of the
> database to convert each character in the string to lowercase. This caused
> the danish lower case o with slash "ø", which in unicode is represented as
> the latin1-readable octets "ø", to be converted to the latin1-readable
> octets "ã¸", which then in turn was (tried) to be interpreted as a unicode
> character - but the octects "ã¸" does not represent a unicode character in
> utf8. The lower case version of "ø" is of course just itself.

This means you have mismatching server encodings and locales configured.
Check SHOW lc_collate and SHOW server_encoding, and then pick a combination
that is compatible.  This will probably mean you have to reinitdb.

Hi Peter,

Thanks for the hint.

I can see that lc_collate (sorting) and lc_ctype (lower-upper conversion) is set to en_DK and I guess that default
encodingfor en_DK is iso88591 or maybe windows1252. Thus my server should have been initialized with en_DK.utf8 or? How
doI find out what the default encoding for the locale en_DK is? I can see, that normally one would sub-specify this by
eitheradding .iso88591 or .utf8, but is windows1252 then default?
 

Because it is clear, that en_DK includes the proper rules for upper-lower conversion of Danish special characters as I
whenconverting from UTF-8 to ISO 8859-1 can use upper() and lower() as expected. And Danish special characters have the
samecode points in latin1 and windows1252.
 

I am not able to reinitdb, as many other databases are running, which might be affected negatively. This means, that
eventhough my database is created WITH ENCODING 'unicode', it is in fact "broken" as the locale does not fully support
unicodestring handling?
 

I wanted to use Unicode, as I expected non-latin1 characters, but this actually means, that if I had any such, some
stringfunctions would not work at all.
 


Regards,
Morten Barklund

-----Original Message-----
From: Peter Eisentraut [mailto:peter_e@gmx.net] 
Sent: Tuesday, July 15, 2008 2:33 PM
To: pgsql-general@postgresql.org
Cc: Morten Barklund
Subject: Re: [GENERAL] Unicode database on non-unicode operating system

Am Dienstag, 15. Juli 2008 schrieb Morten Barklund:
> My problem is, that the lowercase versions of non-ascii characters are
> broken. Specifically I found, that when lower() is invoked on a text with
> non-ascii characters, the operating system's locale is used for converting
> each octet in the string to lowercase in stead of using the locale of the
> database to convert each character in the string to lowercase. This caused
> the danish lower case o with slash "ø", which in unicode is represented as
> the latin1-readable octets "ø", to be converted to the latin1-readable
> octets "ã¸", which then in turn was (tried) to be interpreted as a unicode
> character - but the octects "ã¸" does not represent a unicode character in
> utf8. The lower case version of "ø" is of course just itself.

This means you have mismatching server encodings and locales configured.  
Check SHOW lc_collate and SHOW server_encoding, and then pick a combination 
that is compatible.  This will probably mean you have to reinitdb.

Am Dienstag, 15. Juli 2008 schrieb Morten Barklund:
> I can see that lc_collate (sorting) and lc_ctype (lower-upper conversion)
> is set to en_DK and I guess that default encoding for en_DK is iso88591 or
> maybe windows1252.

It is ISO-8859-1.  There is no support for Windows charmaps on Linux.

> Thus my server should have been initialized with
> en_DK.utf8 or?

Yes, or you should have chosen a different encoding (LATIN1 in your case) when
creating the database.

> How do I find out what the default encoding for the locale en_DK is?

$ LC_ALL=en_DK locale charmap
ISO-8859-1

Note that this is not the "default" encoding, it is the *only* encoding
supported by that locale.

> I can see, that normally one would sub-specify this by either
> adding .iso88591 or .utf8, but is windows1252 then default?

It might be reasonable to use the .iso88591 or .utf8 suffixes if you want to
be explicit, but the unsuffixed locale name is usually just an alias for one
of these.

> I am not able to reinitdb, as many other databases are running, which might
> be affected negatively. This means, that even though my database is created
> WITH ENCODING 'unicode', it is in fact "broken" as the locale does not
> fully support unicode string handling?

Yes.  If you can't reinitdb, then you should recreate the database with
encoding LATIN1.  This won't allow all Unicode characters, obviously, but at
least you get proper behavior for the Danish characters that you need.

Hi Peter,

Thank you once again. That cleared up a lot of confusion for me and my 
co-workers and the next server set up will be with unicode and en_DK.utf8 
to ensure consistency.


Regards,
Morten Barklund

-----Original Message-----
From: Peter Eisentraut [mailto:peter_e@gmx.net] 
Sent: Tuesday, July 15, 2008 3:50 PM
To: pgsql-general@postgresql.org
Cc: Morten Barklund
Subject: Re: [GENERAL] Unicode database on non-unicode operating system

Am Dienstag, 15. Juli 2008 schrieb Morten Barklund:
> I can see that lc_collate (sorting) and lc_ctype (lower-upper conversion)
> is set to en_DK and I guess that default encoding for en_DK is iso88591 or
> maybe windows1252.

It is ISO-8859-1.  There is no support for Windows charmaps on Linux.

> Thus my server should have been initialized with 
> en_DK.utf8 or?

Yes, or you should have chosen a different encoding (LATIN1 in your case) when 
creating the database.

> How do I find out what the default encoding for the locale en_DK is?

$ LC_ALL=en_DK locale charmap
ISO-8859-1

Note that this is not the "default" encoding, it is the *only* encoding 
supported by that locale.

> I can see, that normally one would sub-specify this by either 
> adding .iso88591 or .utf8, but is windows1252 then default?

It might be reasonable to use the .iso88591 or .utf8 suffixes if you want to 
be explicit, but the unsuffixed locale name is usually just an alias for one 
of these.

> I am not able to reinitdb, as many other databases are running, which might
> be affected negatively. This means, that even though my database is created
> WITH ENCODING 'unicode', it is in fact "broken" as the locale does not
> fully support unicode string handling?

Yes.  If you can't reinitdb, then you should recreate the database with 
encoding LATIN1.  This won't allow all Unicode characters, obviously, but at 
least you get proper behavior for the Danish characters that you need.