Thread: 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