Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id AD4EB9FA6BB for ; Sun, 22 Jul 2007 02:54:15 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 36791-10 for ; Sun, 22 Jul 2007 02:54:09 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.179]) by postgresql.org (Postfix) with ESMTP id 1428C9FA6B5 for ; Sun, 22 Jul 2007 02:54:10 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id d32so2981235pye for ; Sat, 21 Jul 2007 22:54:10 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding; b=BP9vtbUGn+lN+fjC11Y4o7TFvFUJPZPBbIoLe5iJObll9h1Jm7FanHevE9xh+dcgUR4hFqT2u/Br4djuW4Dw6XXlf5idJWE4S75MZAKnFv6kDH0Zi5NUZvChRODZhlgrVZFcpR5R5rbhF59LPysCHr27YrGCiSUzwxkD109rgRc= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding; b=gGljbRNHKlnr1OFu7iTiDal81AY7RzxQvqzZJs/REQcCe3xCG9/lWz8MySufA9YgGPuABxxfCovq/maJ62eSIwmy85WFxCpOXwOt3Myr2jpn0M0K1gO9FPM9mpTxsrUrU1XoKUqrvtn4w/THCIvX32JYnGDJf/35ByOy0/q0Z0M= Received: by 10.35.91.10 with SMTP id t10mr3741458pyl.1185083649901; Sat, 21 Jul 2007 22:54:09 -0700 (PDT) Received: from ?172.17.0.199? ( [204.11.129.146]) by mx.google.com with ESMTPS id 17sm19266937nzo.2007.07.21.22.54.08 (version=TLSv1/SSLv3 cipher=RC4-MD5); Sat, 21 Jul 2007 22:54:08 -0700 (PDT) Message-ID: <46A2F0FC.4070501@gmail.com> Date: Sun, 22 Jul 2007 00:54:04 -0500 From: Peter Koczan User-Agent: Thunderbird 2.0.0.4 (X11/20070619) MIME-Version: 1.0 To: Jessica Richard CC: pgsql-admin@postgresql.org Subject: Re: "_" in a serach pattern References: <102980.5844.qm@web56412.mail.re3.yahoo.com> In-Reply-To: <102980.5844.qm@web56412.mail.re3.yahoo.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/242 X-Sequence-Number: 26223 Hi, Jessica, > 1. How do I get rid of the nonstandard warning, but still using the > index search? You have two options. - Turn off the warnings in the postgresql.conf file. Use this with caution (or don't use it at all) as it does pose a potential threat for SQL injections if other options aren't properly set. Read up at http://www.postgresql.org/docs/8.2/static/runtime-config-compatible.html for more detail. - Use escape-string formatting. This is the best practice since it is standards-conforming and more secure. You can do it, for instance, as select name from table where name like 'A!_B%' escape '!'; You can escape with most characters, and it's mostly a matter of personal preference. > > 2. How do I search with a wild card % in the middle of the pattern? > Would varchar(80) or char(80) make a difference about the wild card > search (% in the middle)? I think that postgres is seeing the trailing whitespace on the end of the char type and not accounting for it in the search. In this case, varchar or text types would make a difference since they don't do whitespace padding (unless you force it in). You should remember that if you plan on converting the data type, trim the extraneous whitespace first. However, I would switch to varchar/text so it saves some space and saves you these headaches, unless there's an absolute need for fixed-length char fields. I use text almost exclusively for string data since it's arbitrary-length, I almost never have to worry about overflow or later administration. If you don't want to convert data, you can use the rtrim() function (i.e. "select rtrim(name) from ..."). Peter > ------------------------------------------------------------------------ > Need a vacation? Get great deals to amazing places > on > Yahoo! Travel.