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
Message-ID: <200904030628.54639.aklaver@comcast.net> (raw)
In-Reply-To: <E5F311726E664432BEB109D30F3D3C26@NotebookVolkmar>
References: <E5F311726E664432BEB109D30F3D3C26@NotebookVolkmar>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: aklaver@comcast.net
Subject: Re: Text cast problem
In-Reply-To: <200904030628.54639.aklaver@comcast.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox