BUG #6295: Update fails on empty concatenated strings
The following bug has been logged online:
Bug reference: 6295
Logged by: Jonas Forsman
Email address: jfo123@hotmail.com
PostgreSQL version: 8.4.9
Operating system: Ubuntu 10.04 LTS
Description: Update fails on empty concatenated strings
Details:
Field description (note the field name is "comment") :
comment character varying(2048)
sql:
UPDATE user SET comment= E'[2011-11-16] Note to myself no: 171\\n' ||
comment WHERE userid=1
This query fails if comment is an empty string or null.
This works:
UPDATE user SET comment= E'[2011-11-16] Note to myself no: 171\\n' ||
comment || '' WHERE userid=1
"Jonas Forsman" <jfo123@hotmail.com> writes:
sql:
UPDATE user SET comment= E'[2011-11-16] Note to myself no: 171\\n' ||
comment WHERE userid=1
This query fails if comment is an empty string or null.
Define "fails".
Personally I'm wondering whether you remembered that concatenation of a
null with something else yields null. You might possibly want to spell
the above as SET comment = '...' || coalesce(comment, null) ..., if you
want to pretend that a null is the same thing as an empty string.
regards, tom lane