SQL specifies functions to map NULL values to and from non-NULL values.
COALESCE returns the
first passed non-NULL value:

SELECT COALESCE(NULL, 0);
 coalesce
----------
        0
 
SELECT COALESCE(NULL, 'I am null.');
  coalesce
------------
 I am null.
 
INSERT INTO nullmaptest VALUES ('f'), ('g'), (NULL);
 
SELECT x, COALESCE(x, 'n/a') FROM nullmaptest;
   x    | coalesce
--------+----------
 f      | f
 g      | g
 (null) | n/a
 
SELECT 'a' || COALESCE(NULL, '') || 'b';
 ?column?
----------
 ab
 
SELECT SUM(x), COALESCE(SUM(x), 0) FROM aggtest;
  sum   | coalesce
--------+----------
 (null) |        0

Continue Reading »