Thread: bug/feature with upper function?
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)
2.insert words with all uppercase letters.
3. Try matching the word with "select * from table where
upper(columnname) = 'word'"
I get a result of zero every time.
db=# SELECT * from upp_test ;
uname
----------------------
KAT
KAT
KAT
KAT
KAT
(5 rows)
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?
Vincent
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
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';
Thanks to everyone that replied. I am adjusted my queries and tables accordingly. You must admit that the answer was a little non-intuitive. 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) > 2.insert words with all uppercase letters. > 3. Try matching the word with "select * from table where > upper(columnname) = 'word'" > > I get a result of zero every time. > > > > db=# SELECT * from upp_test ; > uname > ---------------------- > KAT > KAT > KAT > KAT > KAT > (5 rows) > > > > 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? > > Vincent > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html