Thread: LIKE with no wildcards problem
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? 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)
John, > 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? > catalog^> create table foo ( bar char(8) ) > CREATE > You created the column bar as CHAR(8), which means what's actuallybeing stored is "abc " not "abc". I think you wantto use VARCHARinstead. -Josh
On Thursday 28 February 2002 19:54, 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? > > 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) How about: catalog^> SELECT bar, length(bar) from foo; bar | length ----------+--------abc | 82.20 | 8 (2 rows) The CHARACTER type is always padded to the maximum length, viz: http://www.postgresql.org/idocs/index.php?datatype-character.html Ian Barwick
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