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 akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
