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. ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
