From herbst@gc-i.de Fri Apr 3 13:14:05 2009 Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 84CC8633C39 for ; Fri, 3 Apr 2009 10:14:05 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 18188-10 for ; Fri, 3 Apr 2009 10:13:53 -0300 (ADT) X-Greylist: delayed 17:42:07.837936 by SQLgrey-1.7.6 Received: from mout3.freenet.de (mout3.freenet.de [195.4.92.93]) by mail.postgresql.org (Postfix) with ESMTP id 9C3026332F9 for ; Fri, 3 Apr 2009 10:14:00 -0300 (ADT) Received: from [195.4.92.14] (helo=4.mx.freenet.de) by mout3.freenet.de with esmtpa (ID herbst@gc-i.de) (port 25) (Exim 4.69 #79) id 1LpJaG-0002MB-CH for pgsql-sql@postgresql.org; Thu, 02 Apr 2009 11:51:40 +0200 Received: from [41.208.150.42] (port=1166 helo=NotebookVolkmar) by 4.mx.freenet.de with esmtpa (ID herbst@gc-i.de) (port 587) (Exim 4.69 #79) id 1LpJaA-00065Q-UF for pgsql-sql@postgresql.org; Thu, 02 Apr 2009 11:51:35 +0200 From: "Volkmar Herbst" To: Subject: Text cast problem Date: Thu, 2 Apr 2009 09:51:30 -0000 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0003_01C9B378.9B798690" X-Mailer: Microsoft Office Outlook 11 Thread-Index: AcmzeJkUjyZRS3l+SVep+y8GkV+nhw== X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200904/29 X-Sequence-Number: 32375 This is a multi-part message in MIME format. ------=_NextPart_000_0003_01C9B378.9B798690 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Dear all-=20 I encountered the following problem: =20 select * from parcel where number =3D '255 '=20 gives me 1 row but=20 =20 select * from parcel where number =3D '255 ' ::text does give me 0 rows. The cast removes the trailing empty character. =20 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 = suggestions highly welcomed! =20 Volkmar =20 ------=_NextPart_000_0003_01C9B378.9B798690 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable

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 suggestions highly welcomed!

 

Volkmar

 

------=_NextPart_000_0003_01C9B378.9B798690-- From aklaver@comcast.net Fri Apr 3 13:29:03 2009 Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 728D6632B96 for ; Fri, 3 Apr 2009 10:29:03 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 21489-09 for ; Fri, 3 Apr 2009 10:28:53 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from QMTA14.emeryville.ca.mail.comcast.net (qmta14.emeryville.ca.mail.comcast.net [76.96.27.212]) by mail.postgresql.org (Postfix) with ESMTP id 9694C63281F for ; Fri, 3 Apr 2009 10:28:57 -0300 (ADT) Received: from OMTA07.emeryville.ca.mail.comcast.net ([76.96.30.59]) by QMTA14.emeryville.ca.mail.comcast.net with comcast id b0ar1b0051GXsucAE1Uxf8; Fri, 03 Apr 2009 13:28:57 +0000 Received: from tucker.local ([24.17.164.54]) by OMTA07.emeryville.ca.mail.comcast.net with comcast id b1Uv1b00K1Ak4z48T1UwcK; Fri, 03 Apr 2009 13:28:57 +0000 From: Adrian Klaver 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: In-Reply-To: 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> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.179 tagged_above=0 required=5 tests=AWL=0.179 X-Spam-Level: X-Archive-Number: 200904/30 X-Sequence-Number: 32376 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