X-Original-To: pgsql-novice@postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 0D7829251F2; Sun, 25 May 2003 16:01:11 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193:10024]) (amavisd-new) with ESMTP id 16971-10; Sun, 25 May 2003 16:00:43 -0400 (EDT) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by developer.postgresql.org (Postfix) with ESMTP id AC2939251D9; Sun, 25 May 2003 16:00:42 -0400 (EDT) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 3067370; Sun, 25 May 2003 13:00:45 -0700 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: , , Subject: Re: Case Insensitive Searching? Date: Sun, 25 May 2003 12:58:54 -0700 User-Agent: KMail/1.4.3 References: <001301c32169$54d90fa0$5011a8c0@micronpc> In-Reply-To: <001301c32169$54d90fa0$5011a8c0@micronpc> MIME-Version: 1.0 Content-Transfer-Encoding: 8bit Message-Id: <200305251258.54689.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new X-Archive-Number: 200305/226 X-Sequence-Number: 7077 Ben, > So the question is, how do I perform a case insensitive search? Is there a > DB setting I can set to ignore the case? To the latter: No. To the former, there are four ways that you can do a case-insensitive search in PostgreSQL: 1) Using ILIKE: SELECT * FROM sometable WHERE textfield ILIKE 'value%'; 2) Using Regexp operators (see Functions and Operators in the docs): SELECT * FROM sometable WHERE textfield ~* 'value'; 3) Using UPPER() or LOWER() to change the case of the field before comparison; this approach can be better than 1) or 2) because these functions may be indexed, and thus if you are doing a "begins with" or "exact match" search your query may be indexed: SELECT * FROM sometable WHERE UPPER(textfield) LIKE (UPPER('value') || '%'); 4) If most of your searches are "anywhere in field" searches on large text fields, I'd reccomend a look at the two "full text search" tools available in PostgreSQL, one in the /contrib of your source, the second from openFTS.org. Overally, I would strongly recommend that you buy and read an introcductory PostgreSQL book before proceeding further with your project. -- Josh Berkus Aglio Database Solutions San Francisco