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

Re: Re: case insensitive database ?

From: Joel Burton <jburton(at)scw(dot)org>
To: Leandro Fanzone <leandro(at)hasar(dot)com>
Cc: "'pgsql-novice(at)postgresql(dot)org'" <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Re: case insensitive database ?
Date: 2001-04-25 16:59:09
Message-ID: Pine.LNX.4.21.0104251256120.4608-100000@olympus.scw.org (view raw or flat)
Thread:
Lists: pgsql-novice
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 MauchDate: 2001-04-25 17:13:22
Subject: pg_dump libtermcap.so.2 not found
Previous:From: David OlbersenDate: 2001-04-25 16:43:05
Subject: Re: SELECT performance problem on a join

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