Received: from shaked.cc.openu.ac.il (shaked.cc.openu.ac.il [147.233.145.65]) by hub.org (8.9.1/8.9.1) with ESMTP id IAA29511 for ; Tue, 15 Dec 1998 08:13:01 -0500 (EST) (envelope-from herouth@oumail.openu.ac.il) Received: from [147.233.159.109] (herouth@mac-herouth.pc.openu.ac.il [147.233.159.109]) by shaked.cc.openu.ac.il (8.8.8/8.8.8) with ESMTP id PAA16481; Tue, 15 Dec 1998 15:11:21 +0200 (IST) X-Sender: herouth@shaked.cc.openu.ac.il Message-Id: In-Reply-To: References: <010001be270b$0070f980$8355e5ce@tesla.base2inc.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Date: Tue, 15 Dec 1998 15:13:03 +0200 To: Remigiusz Sokolowski , Frank Morton From: Herouth Maoz Subject: Re: [SQL] Newbie questions Cc: pgsql-sql@postgreSQL.org At 10:26 +0200 on 14/12/98, Remigiusz Sokolowski wrote: > > select * from animals where id='dogs' ignore case; > > look at operators in docs > ~~ - LIKE operator > ~* - match(regex), case insensitive operator > I have no idea which is better > Rem > p.s. I could make some mistakes in this examples - check in amnual or docs The like and regexp do a match rather than an equality test. For an exact equality ignoring case, simply use SELECT * FROM animals WHERE lower( id ) = 'dogs'; Note that this means an index on the id column will NOT be used, because each value has to be converted to lower before testing. When I needed this to be an indexed search, I added a column, called, say "lower_id", which contains the lowercase version of the value in the "id" column. Then I indexed that column and then you can ask WHERE lower_id = 'dogs' and get a fast answer. There is also the possibility of creating a functional index, but I haven't managed to cause this sort of index to "kick in" on my version of postgres (6.2.1). Herouth -- Herouth Maoz, Internet developer. Open University of Israel - Telem project http://telem.openu.ac.il/~herutma