Query help with string concatenation and NULL

I have a query that looks like



which does what I want (appends REQUESTING UPDATE to whatever is in comments) if emp_skill.comments is not null, however if it is null then the update does nothing even though phpPgAdmin tells me that the expected number of rows were affected. I was just wondering what's the best way of getting around this (without having to run another query that checks for NULLs) and also, why the affected number of rows is greater than 0 if nothing actually changes. Is it because concatenating a string with NULL yields NULL?

This is on RH 7.2 on pg 7.1.2 (yeah I know I need to upgrade )

Thanks,
-b

Indeed, since NULL is by definition not a value, there is no way to concatenate anything to it. Let's just log into our psql terminal:

So you see that concatenating a string to an empty string works, but concatenating to NULL does not work. You have two choices:

1. Make this a NOT NULL column, with a default value of '' (empty string), Thus, you can effectively treat an empty string as "nothing" for your programming logic, but you can still concatenate a value to it.

2. Add some logic in your query (perhaps using CASE) to check for a NULL, and if it encounters a null, it replaces, instead of concatenating.

My philosophy is to never allow NULL in my table definitions. I agree with C.J. Date and a few other database thinkers, who believe it was a mistake for the SQL standard to support NULL at all. For relational database operations, it introduces all kinds of "gotchas" like the one you encountered above, without providing any great value, other than complicating things.

There are a great many potential problems in more advanced queries, if nulls are involved. I won't go into detail here, but try it: play with different kinds of joins, aggregate queries, WHERE clauses etc... with and without nulls and you will see how nulls can affect the query results. sometimes providing unexpected results, especially for statistics.

I'll try looking into CASE here. BTW upon your recommendations I picked up Date's, An Introduction to Database Systems and while it isn't the easiest reading I've done, I've found it pretty valuable reference in the few chapters I've read so far.

UPDATE
CASE worked like a charm here's my new query in case it helps anyone else:



Thanks again,
-b

I just started reading Foundation for Future Database Systems (The Third Manifesto) by Date. Very interesting reading, hazy place where object-oriented programming and relational data meet.


Glad it worked for you. PostgreSQL is an awesome tool, isn't it ?

Cheers

I see now that Oracle actually treats NULLs incorrectly, thus encouraging all sorts of bad programming logic. Here is also another example of the kind of trouble you can get into by depending on Nulls; from the PostgreSQL mailing lists:

Yeah, I've been going through some of my predecessor's code trying to find spots where he may have used '' and NULL interchangeably. Some hard to diagnose bugs have been squashed now . Nothing against my predecessor as he did good, work, but I think he probably just picked up on the NULL / '' differences a little bit later on in development.

Too bad, there's no easy way to distinguish between NULL and '' when viewing data in phppgadmin (except of course by selecting based on NULL and ''), I could've caught onto this sooner .

BTW, have you started posting to dbforums as well? I thought I saw a post by you there.

-b

Well.... every once in awhile, I troll through the other forums to fix all the unanswered PostgreSQL questions. Just doing my good duty as a netizen .