Re: Best way to use indexes for partial match at

On Wed, 2005-11-09 at 15:30, Andrus wrote:
> Scott,
>
> >> I'm searching a way to use Postgres regular index for this.
> >
> > Easy, do what those other databases do. Setup your database to not use
> > a locale.
> >
> > initdb --locale=C
> >
> > and you're golden.
>
> thank you.
>
> My language has letters in with correstonding upper case letters
>
>
> I need to prevent entering of duplicate customer names into database.
> For this I created unique index on UPPER(customer_name)
>
> I need also search for customer name in case-insensitive way. For this I use
> ILIKE operator.
>
> Those two features are working in Postgres 8.1 when I use non-C locale.
> If I switch to C locale, they will not work.
>
> My current database, Microsoft Visual Foxpro implements this functionality.
>
> How to implement this functionality in Postgres if I switch to C locale ?

You can't. You have conflicting desires. PostgreSQL IS NOT FOXPRO. If
you want to use foxpro, then do so. If you want to use PostgreSQL, then
you'll either have to accept that you need to make certain accomodations
to use it with a non-C locale, or accept a C locale and its limitations.

You say that Foxpro implements this functionality, but are you sure that
it gets things like collation correct? I.e. does it truly understand
all the rules for what comes before something else in your language?
Locales are a complex and difficult thing to get exactly right, and
while, at first blush, Foxpro may seem to do the right thing, you may
find it isn't doing EVERYTHING exactly right, and still having good
performance.

Then again, maybe it is.

But PostgreSQL is limited to working either in a C locale and
automatically using indexes for like 'abc%' queries but getting
collation wrong, or working in the correct locale, not using indexes for
like 'abc%', having to use the special class operator if you want likes
to work, and getting the collation correct.

If that doesn't work for you, your only real choice is to either use
another database, or start hacking to make PostgreSQL the database you
want it to be.

It's not a simple problem, and there is no simple answer. And if you
expect any database to not have things like this in it to deal with, you
just haven't looked very hard at any of them. They've all got warts.
And sometimes, one db is just not a good fit.

Perhaps full text searching could help you out here? Not sure.