Received: from icebox.venux.net (icebox.venux.net [216.120.166.10]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA38861 for ; Wed, 17 May 2000 10:29:43 -0400 (EDT) (envelope-from mitch@venux.net) Received: from servbox.venux.net (matrix.venux.net [216.120.166.5]) by icebox.venux.net (Postfix) with ESMTP id 1D40A26209; Wed, 17 May 2000 10:33:22 -0400 (EDT) Received: from doot (doot.venux.net [10.0.0.65]) by servbox.venux.net (Postfix) with SMTP id 9BAFE2BD82; Wed, 17 May 2000 10:32:05 -0400 (EDT) Message-ID: <00d901bfc00c$a3a36c00$4100000a@venux.net> From: "Mitch Vincent" To: "gav" , References: <11494.000517@nlr.ru> Subject: Re: lower() for varchar data by creating an index Date: Wed, 17 May 2000 10:31:50 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2615.200 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200 X-Archive-Number: 200005/26 CREATE function lower(varchar) returns text as 'lower' language 'internal' with (iscachable); It's a bit slower than if it was actually in the backend but it's a very tiny difference. I spoke to Tom Lane about this, it's on the TODO list I believe for 7.1. In the meantime the above will work great.. - Mitch "The only real failure is quitting." ----- Original Message ----- From: Alex Guryanow To: Sent: Wednesday, May 17, 2000 3:51 AM Subject: [SQL] lower() for varchar data by creating an index > Hi, > > I have a table called t1 with field f1 of type varchar(40): > > CREATE TABLE t1 (f1 varchar(40)); > > To make a case insensitive search I build the query like > > SELECT f1 FROM t1 WHERE lower( f1 ) LIKE 'alex%'; > > This works fine. But when I try to make an index to speed up the query using the command > > CREATE INDEX t1_f1_idx ON t1 (lower(f1)); > > I receive the following error: > > ERROR: DefineIndex: function 'lower(varchar)' does not exist > > Why by executing the query the function 'lower(varchar)' exists and by creating the index don't? > > I use Postgres 7.0. > > Best regards, > Alex > > >