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

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