4.22

Show how to express the coalesce function using the case construct.


The following is taken from postgresql 14.1 documentation in section 9.18.2.

COALESCE(value [, ...])

The COALESCE function returns the first of its arguments that is not null.
Null is returned only if all arguments are null. 

If a query is written using coalesce function as:-

SELECT COALESCE(
    d1,
    d2,
    .
    .
    .
    dn,
) FROM r

It can be rewritten as:-

SELECT 
    CASE
        WHEN d1 IS NOT NULL THEN d1
        WHEN d2 IS NOT NULL THEN d2
        .
        .
        .
        WHEN dn IS NOT NULL THEN dn
        ELSE NULL
    END
FROM r