Re: Controlling locale and impact on LIKE statements

On 9/5/07, Alvaro Herrera <alvherre(at)commandprompt(dot)com> wrote:
> Martin Langhoff escribió:
>
> > As I have a Pg install where the locale is already en_US.UTF-8, and
> > the database already exists, is there a DB-scoped way of controlling
> > the locale?
>
> Not really.

Ah well. But I do have to wonder why... if each database can have its
own encoding, that is likely to be matched with a locale. Isn't that
the main usage scenario? In fact, with unicode encodings, it's likely
that all your DBs are utf-8 encoded, but each may have its own locale.

And yet, right now it's all affected by the locale the cluster was
init'd under. In my case, old Pg installations have been upgraded a
few times from a Debian Sarge (C locale). Newer DB servers based on
ubuntu are getting utf-8-ish locales. And all this variation is
impacting something that should be per DB...

Is this too crazy to ask? ;-)

> You are right and Eloy is wrong on that discussion. There is not
> anything the DB can do to use the regular index if the locale is not C
> for LIKE queries. There are good reasons for this. There's not much
> option beyond creating the pattern_ops index.

Are the reasons *really* good? ;-)

I can see that LIKE 'foo%' is implemented as a combined
greater-than/less-than clause, which is collation dependent. But why
can't I say "for this query, assume C collation, even if you've been
init'd under a utf-8 locale"? That'd save us a whole lot of trouble...

cheers,

martin