Re: LIKE, "=" and fixed-width character fields

Dmitry Teslenko wrote:
> Hello!
> There's table:
> CREATE TABLE table1 (
>     field1 CHARACTER(10),
>     ...
> );
>
> Then there's record: INSERT INTO table1(field1, ..) VALUES ('111', ...);
>
> Then I query it:
> SELECT * FROM table1 WHERE field1 <operator> '111';
>
> When <operator> is LIKE no records matches query, when <operator> is =
> my record matches query. Why? And Does this behavior varies from
> PostgreSQL 7.4 to 8.1?

You're comparing a 3-character value '111' of type text to a
10-character one (whatever is in field1). That's probably not a sensible
thing to do. You haven't got '111' as a value, you've got '111' with 7
trailing spaces. Search for that and you'll find it.

It works for the '=' because the right-hand side will be converted to a
character(10) before the comparison. You can't do that with LIKE because
the right-hand side isn't characters, it's a pattern to search for.

richardh=> SELECT * FROM chartbl WHERE c LIKE '111';
 c
---
(0 rows)

richardh=> SELECT * FROM chartbl WHERE c LIKE '111       ';
     c
------------
 111
(1 row)

richardh=> SELECT * FROM chartbl WHERE c LIKE '111%';
     c
------------
 111
(1 row)

--
  Richard Huxton
  Archonet Ltd