Re: LIKE and SIMILAR TO

From: Ivan Sergio Borgonovo <mail(at)webthatworks(dot)it>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: LIKE and SIMILAR TO
Date: 2008-08-22 12:00:48
Message-ID: 20080822140048.675666b8@dawn.webthatworks.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-general

On Fri, 22 Aug 2008 16:43:47 +0530
"c k" <shreeseva(dot)learning(at)gmail(dot)com> 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.

I was going to suggest to create a functional index
create index on sometable using btree (upper(somecolumn));

select * from sometable where upper(somecolumn)
like '%' || upper(somestring) || '%';

but indexes aren't going to work if you're searching with a prefixed
%.

I was wondering what are the performances of postgresql vs. MySQL in
such case.
Anyway I'd tweak postgresql.conf before complaining it is slower
than MySQL. I've been surprised as well at how postgresql can be
fast.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it

In response to

Browse pgsql-admin by date

  From Date Subject
Next Message CG 2008-08-22 13:33:50 Pg/CyberCluster test results
Previous Message c k 2008-08-22 11:43:19 Re: [GENERAL] LIKE and SIMILAR TO

Browse pgsql-general by date

  From Date Subject
Next Message Richard Huxton 2008-08-22 13:28:43 Re: problem with foreign keys + data-only backup
Previous Message c k 2008-08-22 11:43:19 Re: [GENERAL] LIKE and SIMILAR TO