Error in SELECT clause with UPPER function

Started by Pascal Rempenaultover 23 years ago3 messagesgeneral

prempenault@hubwoo.com

over 23 years ago

1) Using
PostgreSQL 7.2.3 on i586-pc-linux-gnu, compiled by GCC 2.95.3

2) Description

track=# select loginname, upper(loginname) from tperson;
loginname | upper
------------+------------
admin | ADMIN
prempena | PREMPENA
(2 rows)

track=# select loginname, upper(loginname) from tperson where
upper(loginname)='ADMIN';
loginname | upper
-----------+-------
(0 rows)

3) Expected result
loginname | upper
------------+------------
admin | ADMIN
(1 row)

If someone can help to understand why ï¿½

Thanks in advance.

Pascal

Lee Crampton

lee@avbrief.com

over 23 years ago

In reply to: Pascal Rempenault (#1)

Re: Error in SELECT clause with UPPER function

Sorry, can't help with 7.2.3 but it does work with 7.2.1 in similar
circumstances:

version
-------------------------------------------------------------
PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.96

psql avbrief -c "select userid,upper(userid) from users where
userid='sdclee'"
userid | upper
--------+--------
sdclee | SDCLEE
(1 row)

psql avbrief -c "select userid,upper(userid) from users where
upper(userid)='SDCLEE'"

userid | upper
--------+--------
sdclee | SDCLEE
(1 row)

However, looking at your output, it looks like 'ADMIN' may be 'ADMIN ', so
check that out.
To prove it, how about :

track=# select loginname, upper(loginname) from tperson where
upper(loginname) like 'ADMIN%';

Hope that helps
Lee Crampton

""Pascal Rempenault"" <prempenault@hubwoo.com> wrote in message
news:NEBBIINNELNNLHDBPCCIMEAADLAA.prempenault@hubwoo.com...

This is a multi-part message in MIME format.

------=_NextPart_000_0017_01C28A4A.994F01A0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 8bit

1) Using
PostgreSQL 7.2.3 on i586-pc-linux-gnu, compiled by GCC 2.95.3

2) Description

track=# select loginname, upper(loginname) from tperson;
loginname | upper
------------+------------
admin | ADMIN
prempena | PREMPENA
(2 rows)

track=# select loginname, upper(loginname) from tperson where
upper(loginname)='ADMIN';
loginname | upper
-----------+-------
(0 rows)

3) Expected result
loginname | upper
------------+------------
admin | ADMIN
(1 row)

If someone can help to understand why .

Thanks in advance.

Pascal

------=_NextPart_000_0017_01C28A4A.994F01A0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<html xmlns:o=3D"urn:schemas-microsoft-com:office:office"

xmlns:w=3D"urn:sc=

hemas-microsoft-com:office:word"

xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=3DContent-Type content=3D"text/html;

charset=3Diso-8859-1">

<meta name=3DProgId content=3DWord.Document>
<meta name=3DGenerator content=3D"Microsoft Word 9">
<meta name=3DOriginator content=3D"Microsoft Word 9">
<link rel=3DFile-List href=3D"cid:filelist.xml@01C28A4A.95D56B40">

<style>

</style>
</head>

<body lang=3DFR style=3D'tab-interval:35.4pt'>

<div class=3DSection1>

<font size=3D2 color=3Dblack face=3D"Courier

New"><span

lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier

New";color:blac=

k;
mso-ansi-language:EN-GB'>1) Using  <font size=3D1

color=

=3Dblack
face=3DTahoma><span lang=3DEN-GB

style=3D'font-size:8.5pt;font-family:Tahom=

a;
color:black;mso-ansi-language:EN-GB'><o:p></o:p>

<font size=3D2 color=3Dblack face=3D"Courier

New"><span

lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier

New";color:blac=

k;
mso-ansi-language:EN-GB'>PostgreSQL 7.2.3 on i586-pc-linux-gnu, compiled

by=

GCC
2.95.3<span lang=
=3DEN-GB

style=3D'font-size:8.5pt;font-family:Tahoma;color:black;mso-ansi-language:E=

N-GB'><o:p></o:p>

<span

lang=

=3DEN-GB

style=3D'font-size:8.5pt;font-family:Tahoma;color:black;mso-ansi-language:E=

N-GB'> <o:p></o:p>

<font size=3D2 color=3Dblack face=3D"Courier

New"><span

lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier

New";color:blac=

k;
mso-ansi-language:EN-GB'><![if

!supportEmptyParas]> <![endif]><o:p></o=

:p>

<font size=3D2 color=3Dblack face=3D"Courier

New"><span

lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier

New";color:blac=

k;
mso-ansi-language:EN-GB'>2) Description<o:p></o:p>

<span

lang=

=3DEN-GB

style=3D'font-size:8.5pt;font-family:Tahoma;color:black;mso-ansi-language:E=

N-GB'><![if

!supportEmptyParas]> <![endif]><o:p></o:p></=

p>

<font size=3D2 color=3Dblack face=3D"Courier

New"><span

lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier

New";color:blac=

k;
mso-ansi-language:EN-GB'>track=3D# select loginname, upper(loginname) from
tperson; 
 loginname  |   upper 
------------+------------ 
 admin      | ADMIN 
 prempena   | PREMPENA 
(2 rows)<span

lang=

=3DEN-GB

style=3D'font-size:8.5pt;font-family:Tahoma;color:black;mso-ansi-language:E=

N-GB'><o:p></o:p>

<span

lang=

=3DEN-GB

style=3D'font-size:8.5pt;font-family:Tahoma;color:black;mso-ansi-language:E=

N-GB'> <o:p></o:p>

<font size=3D2 color=3Dblack face=3D"Courier

New"><span

lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier

New";color:blac=

k;
mso-ansi-language:EN-GB'>track=3D# select loginname, upper(loginname) from
tperson where upper(loginname)=3D'ADMIN'; 
 loginname | upper 
-----------+------- 
(0 rows)<o:p></o:p>

<font size=3D2 color=3Dblack face=3D"Courier

New"><span

lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier

New";color:blac=

k;
mso-ansi-language:EN-GB'><![if

!supportEmptyParas]> <![endif]><o:p></o=

:p>

<font size=3D2 color=3Dblack face=3D"Courier

New"><span

lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier

New";color:blac=

k;
mso-ansi-language:EN-GB'><![if

!supportEmptyParas]> <![endif]><o:p></o=

:p>

<font size=3D2 color=3Dblack face=3D"Courier

New"><span

lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier

New";color:blac=

k;
mso-ansi-language:EN-GB'>3) Expected result<span lang=3DEN-GB

style=3D'font-size:8.5pt;fon=

t-family:
Tahoma;color:black;mso-ansi-language:EN-GB'><o:p></o:p>

<font size=3D2 color=3Dblack face=3D"Courier

New"><span

lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier

New";color:blac=

k;
mso-ansi-language:EN-GB'> loginname  |   upper 
------------+------------ 
 admin      | ADMIN 
(1 row)<span

lang=

=3DEN-GB

style=3D'font-size:8.5pt;font-family:Tahoma;color:black;mso-ansi-language:E=

N-GB'><o:p></o:p>

<span

lang=

=3DEN-GB

style=3D'font-size:8.5pt;font-family:Tahoma;color:black;mso-ansi-language:E=

N-GB'> <o:p></o:p>

<font size=3D2 color=3Dblack face=3D"Courier

New"><span

lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier

New";color:blac=

k;
mso-ansi-language:EN-GB'>If someone can help to understand why

…<o:p>=

</o:p>

<font size=3D2 color=3Dblack face=3D"Courier

New"><span

lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier

New";color:blac=

k;
mso-ansi-language:EN-GB'><![if

!supportEmptyParas]> <![endif]><o:p></o=

:p>

<font size=3D2 color=3Dblack face=3D"Courier

New"><span

lang=3DEN-GB style=3D'font-size:10.0pt;font-family:"Courier

New";color:blac=

k;
mso-ansi-language:EN-GB'>Thanks in advance.<span lang=3DEN-GB

style=3D'font-size:8.5pt;fon=

t-family:
Tahoma;color:black;mso-ansi-language:EN-GB'><o:p></o:p>

<span

lang=

=3DEN-GB

style=3D'font-size:8.5pt;font-family:Tahoma;color:black;mso-ansi-language:E=

N-GB'> <o:p></o:p>

<font size=3D2 color=3Dblack face=3D"Courier

New"><span

style=3D'font-size:10.0pt;font-family:"Courier

New";color:black'>Pascal</sp=

an><font size=3D2 color=3Dblack

face=3DAria=

l><span

style=3D'font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:Arial'><o:p=

Show quoted text

</o:p>

</div>

</body>

</html>

------=_NextPart_000_0017_01C28A4A.994F01A0--

Tom Lane

tgl@sss.pgh.pa.us

over 23 years ago

In reply to: Pascal Rempenault (#1)

Re: Error in SELECT clause with UPPER function

"Pascal Rempenault" <prempenault@hubwoo.com> writes:

track=# select loginname, upper(loginname) from tperson where
upper(loginname)='ADMIN';
loginname | upper
-----------+-------
(0 rows)

I'm guessing that there are trailing blanks in loginname (possibly
because it's declared char(n) rather than varchar(n)). The result
of upper() is always of type text, so comparison to it treats the
trailing blanks as significant.

regards, tom lane