Thread: 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

I wrote:
> You might possibly want to spell
> the above as SET comment = '...' || coalesce(comment, null) ...

Sheesh.  coalesce(comment, '') of course.  Need more caffeine.

            regards, tom lane