Nat! bio photo

Nat!

Senior Mull.

Twitter RSS

Github

SQL Databases: A funny distinction between CHAR, VARCHAR with LIKE

I thought this was a horrible Postgres 8.3.5 bug.

create temporary table foo ( nummer character(12) );
insert into foo ( nummer) values( '1848' ) ;
select count(*) from foo where nummer = '1848' ;
-- returns 1
select count(*) from foo where nummer like '1848' ;
-- returns 0
drop table foo; 

Now the Postgres documentation claims :

If pattern does not contain percent signs or underscore, then the pattern only represents the string itself; in that case LIKE acts like the equals operator.

which is obviously not the case here.

To me that looks like an obvious and glaring bug, yet interestingly the same behaviour is also apparent in Oracle 10.1 and supposedly Frontbase. Whereas MySQL 4 and Sybase 12 behave as I would expect them to.

When the database type is changed to varchar, then the behaviour is as expected:

create temporary table bar ( nummer character varying );
insert into bar ( nummer) values( '1848' ) ;
select count(*) from bar where nummer = '1848' ;
-- returns 1
select count(*) from bar where nummer like '1848' ;
-- returns 1
drop table bar;