concatenating null strings

In Postgres, when using the || operator to concatenate fields into one string, if any of the fields contain a null value then the result will entirely be a null value. To avoid this problem, we can use the coalesce function:

SELECT 'Doe' || ', ' || NULL || 'John' AS name
-- result will be null
 
SELECT COALESCE('Doe','') || COALESCE(', ','') || COALESCE(NULL,'') || COALESCE('John','') AS name
-- result will be 'Doe, John'