On Wed, 25 Apr 2001, Leandro Fanzone wrote:
> # select * from table where lower(field) ilike '%something%';
> ERROR: parser: parse error at or near "ilike"
ILIKE is new in 7.1. With ILIKE, you don't have to use lower() -- the
point of ILIKE is to perform the comparison case-insensitively:
SELECT * FROM table WHERE field ILIKE 'foobar';
> Another question: if I have an index on lower(field), does it make the like
> search faster? Excuse me if this question is too ignorant.
Not ignorant at all.
As long as PostgreSQL uses the index, it *should* be a good
performance boost. The subject of when PG decides to use an index, and
whether that always is a speed increase can be complicated -- the
Developer's manual covers some of the fine points.
Joel Burton <jburton(at)scw(dot)org>
Director of Information Systems, Support Center of Washington
pgsql-novice by date
|Next:||From: Thorsten Mauch||Date: 2001-04-25 17:13:22|
|Subject: pg_dump libtermcap.so.2 not found |
|Previous:||From: David Olbersen||Date: 2001-04-25 16:43:05|
|Subject: Re: SELECT performance problem on a join|