Lists: | pgsql-bugs |
---|
From: | "Nat!" <nat(at)mulle-kybernetik(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #4574: LIKE fails on non-varying character with no wildcards |
Date: | 2008-12-09 14:07:03 |
Message-ID: | 200812091407.mB9E73A6011312@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 4574
Logged by: Nat!
Email address: nat(at)mulle-kybernetik(dot)com
PostgreSQL version: 8.3.5
Operating system: Mac OS X 10.4
Description: LIKE fails on non-varying character with no wildcards
Details:
The documentation claims:
http://www.postgresql.org/docs/8.3/interactive/functions-matching.html#FUNCT
IONS-LIKE
"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."
But:
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;
Whereas:
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;
OTOH. Oracle has the same behaviour.
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Nat!" <nat(at)mulle-kybernetik(dot)com> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #4574: LIKE fails on non-varying character with no wildcards |
Date: | 2008-12-09 14:50:00 |
Message-ID: | 14585.1228834200@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
"Nat!" <nat(at)mulle-kybernetik(dot)com> writes:
> 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
The trailing spaces are significant when doing LIKE on a char(n) column.
regression=# select '1848'::character(12) like '1848';
?column?
----------
f
(1 row)
regression=# select '1848'::character(12) like '1848 ';
?column?
----------
t
(1 row)
(By and large, my advice for all such cases is "don't use char(n)".
It has no redeeming social value whatever.)
regards, tom lane