Re: bug/feature with upper function?

On Wednesday 20 Mar 2002 17:00, Vincent Stoessel wrote:
> Hello All,
>
> I am having a serious problem matching text using the upper() function
> in postgres 7.2 (from developer rpms) on  Redhat 7.2
>
>
> How to replicate:
>
> 1. create a table of one column   character(20)

Note this - important

> 2.insert words with all uppercase letters.
> 3. Try matching the word with  "select * from table where
> upper(columnname) = 'word'"
>
> FEC=# SELECT * from upp_test where uname = 'KAT' ;
>          uname
> ----------------------
>   KAT
>   KAT
>   KAT
>   KAT
>   KAT
> (5 rows)

Silent typecast when comparing uname to 'KAT' - don't know what to what.

> db=# SELECT * from upp_test where upper(uname)  = 'KAT' ;
>   uname
> -------
> (0 rows)

Another silent cast, this time we can use "\df upper" in psql to find out
that we're casting uname to type 'text'. Either of the following will work
(note 17 spaces in the second example).

select * from foo where upper(uname)='KAT'::character(20);
select * from foo where upper(uname)='KAT                 ';

> 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?

Nope, though confusing. Are you sure you don't want varchar(20) rather than
character(20) for this column?

--
  Richard Huxton