Thread: PostgreSQL, Mac OS X and locales
Hi. I have a problem with the use of PostgreSQL on Mac OS X (10.4.x) and selects with "order by". When there are german umlauts in the column which is used to order, the sorted result is wrong. As an example, I get: a b ä where it should be: a ä b Had someone here the same problem and solved it? I think the locales, that come with Mac OS X are broken for this, as it seems they don't provide the correct LC_COLLATE sort order. I tried to figure out, how to get a correct LC_COLLATE file but wasn't successful so far. Someone else? I'm using the following configuration: - Mac OS X (Server) 10.4.x and 10.3.x - PostgreSQL 8.0.3 - initdb with encoding set to UTF-8 or ISO-Latin-1 (same results) Some hints? Thank you! cug
Attachment
On Tue, Nov 01, 2005 at 10:56:02PM +0100, Guido Neitzer wrote: > Hi. > > I have a problem with the use of PostgreSQL on Mac OS X (10.4.x) and > selects with "order by". When there are german umlauts in the column > which is used to order, the sorted result is wrong. PostgreSQL should get the same results as the command-line sort for the same values of LC_COLLATE. However, the value is fixed at initdb time so maybe that's what's confusing you. However, MacOS X inherited FreeBSDs locale setup, which is fairly lame. They've made many improvements though so your problem should be fixable. BTW, you didn't actually say what locale you where using... Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
On 01.11.2005, at 23:24 Uhr, Martijn van Oosterhout wrote: > PostgreSQL should get the same results as the command-line sort for > the > same values of LC_COLLATE. However, the value is fixed at initdb time > so maybe that's what's confusing you. Yes, it gets the same results as sort. I have tried several encoding/locale settings with initdb. > However, MacOS X inherited FreeBSDs locale setup, which is fairly > lame. > They've made many improvements though so your problem should be > fixable. > > BTW, you didn't actually say what locale you where using... I mostly use UTF-8, so I have called initdb with "-E UTF-8 -- locale=de_DE.UTF-8". The LC_COLLATE for this locale is a link pointing to "../la_LN.US- ASCII/LC_COLLATE". This is why I don't think they paid much attention to the correct sort order of umlauts. But I have also tried ISO-5589-, ISO-8859-15 and others. No change. May I take an LC_COLLATE file from another system and use this? cug
Attachment
On Wed, Nov 02, 2005 at 07:27:49AM +0100, Guido Neitzer wrote: > The LC_COLLATE for this locale is a link pointing to "../la_LN.US- > ASCII/LC_COLLATE". This is why I don't think they paid much attention > to the correct sort order of umlauts. Ah you noticed that. Yes, many of the UTF-8 locales in FreeBSD simply point to the ASCII versions which doesn't exactly work very well. > But I have also tried ISO-5589-, ISO-8859-15 and others. No change. > May I take an LC_COLLATE file from another system and use this? Only another FreeBSD system, these files are not portable. However, the source files for these are plain text so you can edit them. I beleive the source is in the C library. Find the source files, edit them and recompile. Then just copy the LC_COLLATE file over. You might also want to look at some of the other predefined locale collate orders, maybe someone has put some effert in getting an order right for another language. Hope this helps, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Attachment
On 02.11.2005, at 11:47 Uhr, Martijn van Oosterhout wrote: > Ah you noticed that. Yes, many of the UTF-8 locales in FreeBSD simply > point to the ASCII versions which doesn't exactly work very well. Right. > Only another FreeBSD system, these files are not portable. However, > the > source files for these are plain text so you can edit them. I beleive > the source is in the C library. Find the source files, edit them and > recompile. Then just copy the LC_COLLATE file over. I have to look how to do this. I have build locales a very long time ago, but currently I'm looking where to start ... perhaps with the Darwin sources. We will see. > You might also want to look at some of the other predefined locale > collate orders, maybe someone has put some effert in getting an order > right for another language. I tried a lot of them and the all didn't work for me. cug