Re: Text cast problem

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(at)comcast(dot)net