Postgresql work with null rows after joins

For example, we have next query:

select A.id, A.field > (case when A.cond1 then 
(case when B.field is null then 0 else B.field end) else 
(case when C.field is null then 0 else C.field end) end)
from A left join B on B.A_id = A.id left join C on C.A_id = A.id;

Is there way to simplify work with replacing null to 0?

Answer

Yes, with COALESCE:

COALESCE(column_that_might_be_null, 0)

It accepts multiple arguments and works left to right returning the first non null:

COALESCE(column_that_might_be_null, another_possible_null, third_maybe_null, 0)

Is the equivalent of

CASE 
  WHEN column_that_might_be_null IS NOT NULL THEN column_that_might_be_null 
  WHEN another_possible_null IS NOT NULL THEN another_possible_null
  WHEN third_maybe_null IS NOT NULL THEN third_maybe_null
  ELSE 0
END 

And it is SQL standard so should work on all compliant databases

---

PG also supports ISNULL and IFNULL which work similarly but I don’t usually recommend them over COALESCE because they don’t exist in all databases/don’t necessarily work equivalently and aren’t as flexible because they only accept 2 arguments. For me this isn’t enough to justify saving 2 characters.. (And if you forget about COALESCE, and you end up doing ISNULL(ISNULL(ISNULL(first, second), third), 0) the SQL is more messy)