Обсуждение: BUG #4574: LIKE fails on non-varying character with no wildcards
The following bug has been logged online: Bug reference: 4574 Logged by: Nat! Email address: nat@mulle-kybernetik.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.
"Nat!" <nat@mulle-kybernetik.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