Forum OpenACS Q&A: NULL SQL issues

I would like to get around this NULL issue. Here is the SQL using PG7.1 that updates ONE record. The record will NOT update if the field is NULL but if I set the field to '' or 'any text here' all is well. Here is the code:

data=# update users set admin_note = NULL where user_id=5;
UPDATE 1

data=# update users set admin_note = admin_note || '
' ||
now() || ' My note here' where user_id=5;
UPDATE 1

data=# select admin_note from users where user_id=5;

 admin_note
------------

(1 row)

data=# update users set admin_note = '' where user_id=5;
UPDATE 1

data=# update users set admin_note = admin_note || '
' ||
now() || 'My note here' where user_id=5;
UPDATE 1

data=# select admin_note from users where user_id=5;
             admin_note
-------------------------------------

2002-01-24 16:47:03-05 My note here
(1 row)

So, I want to use the command to add lines to the admin_note field in many records at once like this:

update users set admin_note = admin_note || '
' ||
now() || ' My note here' where user_id > 1000;

The only records updated are those in which there is already text in the field. All the rest of the insert is lost if admin_note is NULL.

Is there a way to do this without making all admin_note fields NOT NULL?

-Bob

..which actually makes sense. One of the possible meanings for NULL is "value not known". If you add something to an unknown value, you'll get another unknown value.

Jade's almost right ... the *only* possible returned value is NULL according to the SQL 92 standard (as opposed to "One of the possible meanings for NULL is 'value not known'  It's the *only* value for NULL that's returned)

"value not null" is *always* the semantic.  Well, SQL92 is wishy-washy on it in many widely-critiqued circumstances but this isn't one of them.

SQL defines three-valued semantics and you need to understand the consequences inside or out to make sense of expressions which include null.

As noted, you can avoid the complexities by whacking your code so nulls never exist (a symptom of SQL92's weaknessesw/inconsistencies more than with the concept itself).