Re: bug/feature with upper function?

On Wed, 20 Mar 2002, Vincent Stoessel wrote:

> I am having a serious problem matching text using the upper() function
> in postgres 7.2 (from developer rpms) on Redhat 7.2
>
> FEC=# SELECT * from upp_test where uname = 'KAT' ;
> uname
> ----------------------
> KAT
> KAT
> KAT
> KAT
> KAT
> (5 rows)
>
> db=# SELECT * from upp_test where upper(uname) = 'KAT' ;
> uname
> -------
> (0 rows)
>
> The reason I am worried about this is that I have a mix of upper and
> lower case words in the real world coumn and I have not been getting
> complete matches. Is this a bug?

You're getting a type conversion with upper (IIRC it's upper(text) returns
text) which is changing the behavior relating to the padding spaces.
It's going to think the upper is 'KAT ' (I think I counted
out the right number of spaces).

In general it's often easiest to make the column varchar rather than char
unless you really are using the padding behavior, but you could possible
get away with an additional upper function:

create function upper(character) returns character as 'select upper(CAST(
$1 as text));' language 'sql';