On Mon, Nov 10, 2008 at 18:14, Richard Huxton <dev@xxxxxxxxxxxx> wrote:
> 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.
got it.
>
> 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)
>
'111%' would also match '1111' and '111anything', wouldn't it?
> --
> Richard Huxton
> Archonet Ltd
>
On Mon, Nov 10, 2008 at 18:27, Tom Lane <tgl@xxxxxxxxxxxxx> wrote:
> Richard Huxton <dev@xxxxxxxxxxxx> writes:
>> Dmitry Teslenko wrote:
>>> 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.
>
> Better yet: use varchar(n) not character(n). character(n) has no
> redeeming social value whatsoever.
>
> regards, tom lane
Okay, next time only varchars, but now I've got this db schema and no
ability to change it.
--
A: Because it messes up the order in which people normally read text.
Q: Why is top-posting such a bad thing?
A: Top-posting.
Q: What is the most annoying thing in e-mail?
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
