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

On Mon, Nov 10, 2008 at 18:14, Richard Huxton <dev(at)archonet(dot)com> 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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Richard Huxton <dev(at)archonet(dot)com> 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?