Mastering COALESCE in PostgreSQL: Fix NULL Arithmetic Results

When a numeric column contains NULL, any arithmetic operation involving that column also returns NULL, which often leads to disappointing results. The COALESCE function can replace NULL with a specified default value, allowing calculations to proceed normally.

COALESCE Syntax

COALESCE(column_name, default_value)

Demo Setup

su - postgres psql music CREATE TABLE test_null(id INT, num1 INT, num2 INT);

Insert Sample Data

INSERT INTO test_null VALUES (1,100,100); INSERT INTO test_null VALUES (2,200,200); INSERT INTO test_null VALUES (3,NULL,100); INSERT INTO test_null VALUES (4,300,NULL); INSERT INTO test_null VALUES (5,NULL,500);

View Table Contents

SELECT * FROM test_null;

Result:

id | num1 | num2
----+------+------
 1  | 100  | 100
 2  | 200  | 200
 3  | NULL | 100
 4  | 300  | NULL
 5  | NULL | 500
(5 rows)

Direct Arithmetic Without COALESCE

SELECT num1 + num2 FROM test_null;

Result shows NULL for rows where either operand is NULL:

?column?
----------
 200
 400
 (NULL rows omitted in display)
(5 rows)

Using COALESCE to Handle NULL

SELECT COALESCE(num1,0) + COALESCE(num2,0) FROM test_null;

Result now returns correct sums for all rows:

?column?
----------
 200
 400
 100
 300
 500
(5 rows)

The default value supplied to COALESCE can be any number appropriate to the domain—zero for generic arithmetic, 36 for human body temperature, 0 for the freezing point, 100 for the boiling point, etc.