From: Adrian Klaver <aklaver@comcast.net>
To: pgsql-sql@postgresql.org
Subject: Re: Text cast problem
Date: Fri, 3 Apr 2009 06:28:54 -0700
User-Agent: KMail/1.9.10
References: <E5F311726E664432BEB109D30F3D3C26@NotebookVolkmar>
In-Reply-To: <E5F311726E664432BEB109D30F3D3C26@NotebookVolkmar>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200904030628.54639.aklaver@comcast.net>

On Thursday 02 April 2009 2:51:30 am Volkmar Herbst wrote:
> Dear all-
>
> I encountered the following problem:
>
>
>
> select * from parcel where number =3D '255 '
>
> gives me 1 row but
>
>
>
> select * from parcel where number =3D '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=E2=80=99t change the way they are generated. What can I do? Any sugge=
stions
> 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 specifie=
d=20
width n, and are stored and displayed that way. However, the padding spaces=
 are=20
treated as semantically insignificant. Trailing spaces are disregarded when=
=20
comparing two values of type character, and they will be removed when=20
converting a character value to one of the other string types. Note that=20
trailing spaces are semantically significant in character varying and text=
=20
values. "


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

>
>
>
> Volkmar


=2D-=20
Adrian Klaver
aklaver@comcast.net