Обсуждение: LIKE, "=" and fixed-width character fields

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?

--
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?

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

Richard Huxton <dev@archonet.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

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

Dmitry Teslenko wrote:
>> 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?

Yes.

I'm guessing what you actually want is varchar(10) rather than char(10)
as a type.

--
  Richard Huxton
  Archonet Ltd