pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
Text cast problem 2+ messages / 2 participants [nested] [flat]
* Text cast problem @ 2009-04-02 09:51 Volkmar Herbst <herbst@gc-i.de> 0 siblings, 1 reply; 2+ messages in thread From: Volkmar Herbst @ 2009-04-02 09:51 UTC (permalink / raw) To: pgsql-sql 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! Volkmar ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Text cast problem @ 2009-04-03 13:28 Adrian Klaver <aklaver@comcast.net> parent: Volkmar Herbst <herbst@gc-i.de> 0 siblings, 0 replies; 2+ messages in thread From: Adrian Klaver @ 2009-04-03 13:28 UTC (permalink / raw) To: pgsql-sql 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2009-04-03 13:28 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2009-04-02 09:51 Text cast problem Volkmar Herbst <herbst@gc-i.de> 2009-04-03 13:28 ` Adrian Klaver <aklaver@comcast.net>
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