So I’m sitting here quietly trying to port some old Oracle 7 view definitions to PostgreSQL 7.4.2. The first few were pretty easy, then I ran into views which were using Oracle specific functions, like decode(). After a some searching I come to find out that this functionality is supported in PostgreSQL by the CASE clause. That turned out to be reasonably straight forward, but I still wasn’t getting correct data back from the view in PostgreSQL. So I trimmed the code back until I found the culprit, rtrim. So that you get an idea of what was happening here is some example code that demonstrates the problem:
SELECT
rpad(
rtrim(first_name) || ' ' ||
rtrim(middle_name) || ' ' ||
rtrim(last_name),
40
) AS name
FROM users
WHERE user_id = 123
This seems innocent enough right? Wrong! For some reason this code was returning NULL even though I knew that there was data in the first_name and last_name columns in the table for that user. I couldn’t find any mention of this anywhere, but when concatenating strings like this, if rtrim is passed a NULL then it NULLS out the whole concatenated string! I don’t know why it does this, but it does. So how do you fix this you might wonder (I did)? One way (I’m hoping there might be other, read easier, ways) is to add a CASE clause checking to see if the value of the column is NULL before passing it to rtrim(). In my example above let us assume that first_name and last_name don’t allow NULL values, but middle_name does (because not everyone has a middle name, or they don’t want other people to know what it is), the resulting code for the CASE solution looks like this:
SELECT
rpad(
rtrim(first_name) || ' ' ||
CASE
WHEN middle_name IS NULL
THEN ''
ELSE rtrim(middle_name)
END || ' ' ||
rtrim(last_name),
40
) AS name
FROM users
WHERE user_id = 123
After writing that out the first time I wasn’t sure it would work, I was worried the parser might not like having a CASE clause in the middle of an rpad between concatenated strings. I didn’t have anything to loose so I went ahead and ran it. Worked like a charm, spit back a string for the name even if the middle_name field was NULL. The moral of our story, beware the swinging battle axe that is rtrim()!
(via Slashdot).
