From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Dmitry Teslenko <dteslenko(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: LIKE, "=" and fixed-width character fields |
Date: | 2008-11-10 15:14:22 |
Message-ID: | 49184FCE.3060805@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andrus | 2008-11-10 15:18:22 | Re: Optimizing IN queries |
Previous Message | Bruno Lavoie | 2008-11-10 14:39:00 | Re: Importing text file into a TEXT field |