Thanks to all, I will try to use tsearch2 with some other index and then reply. Regards, CPK
On Fri, Aug 22, 2008 at 5:01 PM, Tino Wildenhain <[EMAIL PROTECTED]> wrote: > Hi, > > c k wrote: > >> Hello all, >> As we are migrating our ERP application from MySQL to PostgreSQL we have >> some difficulties. One of them is use of Like and Similar to operators. We >> often use LIKE to search a string from front-end without case sensetivity. >> As postgreSQL's LIKE is case sensitive, we tried ILIKE and SIMILAR TO, but >> both are slower than LIKE and we must need case insensitivity. How can we >> get this by increases speed. All search columns are VARCHAR(100)to >> VARCHAR(250). Currently without index. >> > > You could build an index on lower(column) and use lower(column) like ... > this would speed up queries with exact match as well as 'foo%' > e.g. "start with..." match. > > Make sure when you create the database cluster (initdb) you > used the currect locale, otherwise lower() (and ilike) probably > do not work as you might expect. > > For any more complex searches I'd recommend full text index, > for example tsearch2. > > Regards > Tino >
