Ilja Golshtein wrote:
Hi!
I came across following difference
between "LIKE" and "=" regarding
CHARs and VARCHARs
create table aa(f5 char(5), fv varchar(5));
insert into aa values('str1', 'str1');
select count(*) from aa where f5 = fv;
1
select count(*) from aa where f5 like fv;
0
I understand trailing spaces in CHAR are not
significant though I expect DBMS shows
consistent (and, ideally, clearly documented)
behavior.
If you ask me (and it's too late to back out now :-) the whole behaviour
of CHAR(n) is wrong, broken and just a bad idea.
From my point of view in example above
it would be nice to have the same result
for both queries regardless it is 0 or 1.
Of course, I may be wrong. Is there a clear
concept behind the difference between "LIKE"
and "="?
Well, you could argue that LIKE should ignore the trailing spaces (and
the only way to decide is to look at the SQL specs). The problem is,
that if f5=fv then presumably length(f5)=length(fv) and length(f5 ||
'+') = length(f5)+1
Of course, only the first of these is true because the whole idea of
char(n) is badly thought out. Either the value contains spaces or it
doesn't - unfortunately it's neither and both.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
