Received: from double.nlr.ru (double.nlr.ru [212.193.3.3]) by hub.org (8.9.3/8.9.3) with ESMTP id DAA09360 for ; Wed, 17 May 2000 03:51:53 -0400 (EDT) (envelope-from gav@nlr.ru) Received: from GURYANOW ([172.16.2.2]) by double.nlr.ru (8.9.3/8.9.3) with ESMTP id LAA17024 for ; Wed, 17 May 2000 11:51:51 +0400 Date: Wed, 17 May 2000 11:51:49 +0400 From: Alex Guryanow X-Mailer: The Bat! (v1.34a) UNREG / CD5BF9353B3B7091 Reply-To: gav X-Priority: 3 (Normal) Message-ID: <11494.000517@nlr.ru> To: Subject: lower() for varchar data by creating an index Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Archive-Number: 200005/23 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