Skip site navigation (1) Skip section navigation (2)

Re: [GENERAL] LIKE and SIMILAR TO

From: Tino Wildenhain <tino(at)wildenhain(dot)de>
To: c k <shreeseva(dot)learning(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org, pgsql-admin(at)postgresql(dot)org
Subject: Re: [GENERAL] LIKE and SIMILAR TO
Date: 2008-08-22 11:31:05
Message-ID: 48AEA379.7080807@wildenhain.de (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-general
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

In response to

Responses

pgsql-admin by date

Next:From: c kDate: 2008-08-22 11:43:19
Subject: Re: [GENERAL] LIKE and SIMILAR TO
Previous:From: RWDate: 2008-08-22 11:24:21
Subject: Re: LIKE and SIMILAR TO

pgsql-general by date

Next:From: c kDate: 2008-08-22 11:43:19
Subject: Re: [GENERAL] LIKE and SIMILAR TO
Previous:From: Terry Lee TuckerDate: 2008-08-22 11:27:56
Subject: Re: Script to export all privileges to csv or similar

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group