Обсуждение: 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.