Thread: Problem cocatenating String (||)

Problem cocatenating String (||)

From
"Thomas Niemeier"
Date:
SGVsbG8gZXZlcnlib2R5IQ0KDQpJJ3ZlIGdvdCBhIHByb2JsZW0gY29uY2F0
ZW5hdGluZyB0d28gZmllbGRzIChjaGFyKDEwKSBub3QgbnVsbCkgaW4gYSBz
ZWxlY3Qgc3RhdGVtZW50LiBMZXQncyBzYXkgdGhlIGZpcnN0IGZpZWxkIHBu
YW1lIGhhcyB0aGUgdmFsdWUNCidVU0VSICAgICAgJyBhbmQgdGhlIHNlY29u
ZCBmaWVsZCBwZmlyc3QgdGhlIHZhbHVlICdKT0UgICAgICAnLiBUaGVuIGEg
InNlbGVjdCBwbmFtZXx8cGZpcnN0IGZyb20gdGFibGUiIHdvdWxkIHJldHVy
biB0aGUgdmFsdWUgJ1VTRVJKT0UnLCB0cmltbWluZyBhbGwgc3BhY2VzIGlu
IHRoZSBmaWVsZHMuIFRoaXMgYmVoYXZpb3VyIHNlZW1zIHRvIGJlIG5ldyBp
biBSZWxlYXNlIDcuNC41LiBSZWxlYXNlIDcuMy4yIHJldHVybnMgJ1VTRVIg
ICAgICBKT0UnLCB3aGljaCBtYWtlcyBtb3JlIHNlbnNlIHRvIG1lLg0KSXMg
dGhlcmUgYW55IHdheSB0byBnZXQgbXkgc3BhY2VzIGJhY2s/IA0KSSdtIHdv
cmtpbmcgb24gU3VTRS1MaW51eCA4LjINCg0KQmVzdCB3aXNoZXMNCg0KVGhv
bWFzIE5pZW1laWVyDQo=

Re: Problem cocatenating String (||)

From
Tom Lane
Date:
"Thomas Niemeier" <thomas.niemeier@case-bielefeld.de> writes:
> I've got a problem concatenating two fields (char(10) not null) in a select statement. Let's say the first field
pnamehas the value 
> 'USER      ' and the second field pfirst the value 'JOE      '. Then a "select pname||pfirst from table" would return
thevalue 'USERJOE', trimming all spaces in the fields. This behaviour seems to be new in Release 7.4.5. Release 7.3.2
returns'USER      JOE', which makes more sense to me. 

The general feeling around here is that if you consider trailing spaces
to be significant, you ought to be storing your data as varchar not
char.  The SQL spec is perfectly clear that trailing spaces in char(n)
are not significant for comparison purposes.  It's a bit vague about how
to handle them otherwise, but we've found that supposing them to be
significant for some operations but not others leads to all sorts of
inconsistencies.

            regards, tom lane