Thread: C locale
I've been searching the list archives and the web for a while about which locale is best used with PostgreSQL and I did not find a satisfactory answer so I thought I would ask the list. Right now my locale is en_US, but with this you can not use standard indexes for LIKE queries, so I am left with two options: 1. User special 'operator class' indexes so that my non-C locale database will use indexes for like queries. 2. Reinitialize my database to use the C-locale (not a big deal since I am still at a testing phase of my project).. I was just wondering if there is any thought as to what is the best approach? By switching from en_US to C locales I can avoid using special indexes, but what do I lose? I don't really understand what the C locale is exactly, so I'm not sure if in switching from en_US to C to aviod using operator class indexes something else will stop working. Thanks. Ara Anjargolian
On Tue, 2 Mar 2004, Ara Anjargolian wrote: > I've been searching the list archives and the web for a while about > which locale is best used with PostgreSQL and I did not find a > satisfactory answer so I thought I would ask the list. > > Right now my locale is en_US, but with this you can not use standard > indexes for LIKE queries, so I am left with two options: > > 1. User special 'operator class' indexes so that my non-C locale database > will use indexes > for like queries. > > 2. Reinitialize my database to use the C-locale (not a big deal since I am > still at a testing > phase of my project).. > > > I was just wondering if there is any thought as to what is the best > approach? By switching from en_US to C locales I can avoid using special > indexes, but what do I lose? I don't really understand what the C > locale is exactly, so I'm not sure if in switching from en_US to C to > aviod using operator class indexes something else will stop working. You may just want to set the LC_COLLATE portion of the locale, but anyway... "C" collation is basically byte order collation. "ab" > "Ab", "a b" > "A Z", "A C" < "AB" en_US uses something closer to dictionary sorting, so "a d" < "A Z", "A C" > "AB"
On Tue, 2 Mar 2004, Ara Anjargolian wrote: > I've been searching the list archives and the web for a while about which > locale is best used with PostgreSQL and I did not find a satisfactory answer > so I > thought I would ask the list. > > Right now my locale is en_US, but with this you can not use standard indexes > for LIKE > queries, so I am left with two options: > > 1. User special 'operator class' indexes so that my non-C locale database > will use indexes > for like queries. > > 2. Reinitialize my database to use the C-locale (not a big deal since I am > still at a testing > phase of my project).. > > > I was just wondering if there is any thought as to what is the best > approach? By switching from en_US to C locales I can avoid using special > indexes, > but what do I lose? I don't really understand what the C locale is exactly, > so I'm not sure > if in switching from en_US to C to aviod using operator class indexes > something else > will stop working. The C locale is basically an ASCII locale. I.e. you'll lose the normal US collation, which ignores spaces and some other things. I find that for most of what I'm doing, the C locale is actually preferable.
Ara Anjargolian wrote: > I was just wondering if there is any thought as to what is the best > approach? By switching from en_US to C locales I can avoid using > special indexes, but what do I lose? This is a tradeoff between making your application function correctly (choosing the correct locale) and some implementation detail that costs you nearly nothing (creating another index). Let the former drive your decision.