Thread: Text cast problem

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB" style="font-size:
10.0pt;font-family:Arial">Dear all- </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial">I encountered the following problem:</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-GB" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span lang="EN-GB"
style="font-size:12.0pt">select* from parcel where number = '255 ' </span></font><p class="MsoNormal"><font face="Times
NewRoman" size="3"><span lang="EN-GB" style="font-size:12.0pt">gives me 1 row but </span></font><p
class="MsoNormal"><fontface="Times New Roman" size="3"><span lang="EN-GB" style="font-size:12.0pt"> </span></font><p
class="MsoNormal"><fontface="Times New Roman" size="3"><span lang="EN-GB" style="font-size:12.0pt">select * from parcel
wherenumber = '255 ' ::text</span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span lang="EN-GB"
style="font-size:12.0pt">doesgive me 0 rows. The cast removes the trailing empty character.</span></font><p
class="MsoNormal"><fontface="Times New Roman" size="3"><span lang="EN-GB" style="font-size:12.0pt"> </span></font><p
class="MsoNormal"><fontface="Times New Roman" size="3"><span lang="EN-GB" style="font-size:12.0pt">Why is that?
Unfortunatelythe statements are generated (NPGSQL) and I can’t change the way they are generated. What can I do? Any
suggestionshighly welcomed!</span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span lang="EN-GB"
style="font-size:12.0pt"> </span></font><pclass="MsoNormal"><font face="Times New Roman" size="3"><span lang="EN-GB"
style="font-size:12.0pt">Volkmar</span></font><pclass="MsoNormal"><font face="Times New Roman" size="3"><span
lang="EN-GB"style="font-size:12.0pt"> </span></font></div> 

On Thursday 02 April 2009 2:51:30 am Volkmar Herbst wrote:
> Dear all-
>
> I encountered the following problem:
>
>
>
> select * from parcel where number = '255 '
>
> gives me 1 row but
>
>
>
> select * from parcel where number = '255 ' ::text
>
> does give me 0 rows. The cast removes the trailing empty character.
>
>
>
> Why is that? Unfortunately the statements are generated (NPGSQL) and I
> can’t change the way they are generated. What can I do? Any suggestions
> highly welcomed!

The why is here:
http://www.postgresql.org/docs/8.3/interactive/datatype-character.html

In particular:

"Values of type character are physically padded with spaces to the specified
width n, and are stored and displayed that way. However, the padding spaces are
treated as semantically insignificant. Trailing spaces are disregarded when
comparing two values of type character, and they will be removed when
converting a character value to one of the other string types. Note that
trailing spaces are semantically significant in character varying and text
values. "


What I have done in a similar situation is put a trigger on the table that trims
the strings on INSERT or UPDATE.

>
>
>
> Volkmar



--
Adrian Klaver
aklaver@comcast.net