Redshift : Coalesce Can’t Work As Expected

We’re complementing null value to all zero value like ’00’ on Redshift. Sometimes, I found coalesce function can’t work as we expected. If we use case and len, it can work fine as follows;

-- not work
SELECT coalesce(code_a, '00') FROM test;  --> code_a returns nothing. 

-- works fine
SELECT case when len(code_a)=0 then '00' else code_a end as code_a FROM test;
  --> code_a returns 00 as expected.

So far, I have no idea why coalesce can’t work. Any advice would be appreciated. Thank you.

Answer

There is a difference between '' and NULL — and I should note that this is expected.

You can solve this in one of two ways:

coalese(nullif(code_a, ''), '00')

or using a case expression:

(case when code_a <> '' then code_a else '00' end)