Received: from ece.rice.edu (ece.rice.edu [128.42.4.34]) by postgresql.org (Postfix) with ESMTP id 327CC476059 for ; Thu, 28 Feb 2002 14:46:33 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by ece.rice.edu (Postfix) with ESMTP id 4811668A59; Thu, 28 Feb 2002 13:46:33 -0600 (CST) Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154]) by ece.rice.edu (Postfix) with ESMTP id 5C79E68A57; Thu, 28 Feb 2002 13:46:32 -0600 (CST) Received: from reedstrm by wallace.ece.rice.edu with local (Exim 3.34 #1 (Debian)) id 16gWVc-00037d-00; Thu, 28 Feb 2002 13:46:32 -0600 Date: Thu, 28 Feb 2002 13:46:32 -0600 From: "Ross J. Reedstrom" To: "John J. Allison" Cc: pgsql-sql@postgresql.org Subject: Re: LIKE with no wildcards problem Message-ID: <20020228194632.GD11120@rice.edu> Mail-Followup-To: "Ross J. Reedstrom" , "John J. Allison" , pgsql-sql@postgresql.org References: <200202281854.LAA13770@virga.joss.ucar.edu> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <200202281854.LAA13770@virga.joss.ucar.edu> User-Agent: Mutt/1.3.27i X-Virus-Scanned: by AMaViS snapshot-20010714 X-Archive-Number: 200202/456 X-Sequence-Number: 6877 On Thu, Feb 28, 2002 at 11:54:02AM -0700, John J. Allison wrote: > When doing a select with a LIKE in the where clause, > I always get 0 rows if I do not use a wildcard [_%]. > LIKE should act like = in this case (and the docs say so). > A select without a from correctly returns 't'. > I am using PostgreSQL 7.1.3 on Solaris. > What am I missing? The whitespace. Your field is of type CHAR(n), which pads all strings to the defined width. This behavior is required by the standard. Try using VARCHAR or TEXT instead. Ross > > Thanks, > > John Allison > john@joss.ucar.edu > > > catalog^> create table foo ( bar char(8) ) > CREATE > > catalog^> insert into foo values ( 'abc' ) > INSERT 38413 1 > > catalog^> insert into foo values ( '2.20' ) > INSERT 38414 1 > > catalog^> select * from foo > bar > ---------- > abc > 2.20 > (2 rows) > > catalog^> select * from foo where bar like 'abc' > bar > ----- > (0 rows) > > catalog^> select * from foo where bar like 'abc%' > bar > ---------- > abc > (1 row) > > catalog^> select 'abc' like 'abc' > ?column? > ---------- > t > (1 row) > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster