Forum OpenACS Q&A: NULL SQL issues

Collapse
Posted by MaineBob OConnor on

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

Collapse
Posted by David Walker on
The concatenation operator || returns NULL if one of the items being
concatenated is NULL.  Your best bet is probably to make the admin_note
field default '' and update all the currently NULL fields to equal ''
Collapse
Posted by Jade Rubick on
..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.
Collapse
Posted by Dan Wickstrom on
You could also try using coalesce:

update users set admin_note = coalesce(admin_note,'') || ...
Collapse
Posted by Don Baccus on
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).

Collapse
Posted by MaineBob OConnor on

Thanks Dan, This code works great:

update users set admin_note = coalesce(admin_note,'') || ...

AND it saves me from "NOT NULLing" all the columns in existing tables and setting up a default.

This *may* be unconventional, but for what it's worth, I use NULLs regularly when customizing OACS code. I add a char(1) to an existing table then set it to a value such as "1" for true and NULL for false for various rows. This also allows me to use multiple other "true" values (such as an exclusion value) and is more useful than boolean values traditonally used in OACS code with a trailing "_p" in the column (field) name.

-Bob