Difference between COALESCE and CASE IS NOT NULL

Question

I'm replacing some reports from jasper reports SQL to JAVA EE native query
I found a SQL query (postgres) that has:

    SELECT
        --many rows
        (CASE WHEN description IS NOT NULL THEN description ELSE '' END), --varchar(200)
        COALESCE(house_number, ''), --varchar(20)
        COALESCE(floor_number, ''), --varchar(20)
        (CASE WHEN (power IS NOT NULL AND power > 0) THEN power ELSE 0 END)
   FROM
       --table

Is there any difference using COALESCE and CASE IS NOT NULL?

COALESCE(description, '')

would return a different result on some cases?

or

COALESCE(CAST(power AS TEXT), '0')

or maybe one is faster on some cases than another one?

Answer 1

Under most circumstances, the two are identical. I think the standard defines COALESCE(X, Y) as:

(CASE WHEN X IS NOT NULL THEN X ELSE Y END)

Under most circumstances, this does exactly what you expect.

There is one difference. One interpretation of the standard is the X is evaluated twice when the value is not NULL. This makes no difference under most circumstances (in most cases, the value would be cached anyway).

Postgres does not do this though. It implements a more reasonable interpretation of the standard. In other words, the two are not exactly identical in Postgres, because Postgres is smart enough to evaluate the first expression once, whether it is NULL or not.

You can see the effect if you have a volatile function (one whose value changes). In Postgres, the following returns "x" and "y" about the same number of times:

select v.x, coalesce(case when random() > 0.5 then 'x' end, 'y')
from (values (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) v(x);

Equivalent code in SQL Server returns "y" half the time and "x" and NULL a quarter of the time each -- because SQL Server treats them the two constructs as exactly equivalent and evaluates the first argument twice under some circumstances.

You can see this in the db<>fiddles here and here.

However, SQL