PostgreSQL: How to Replace Null Values with Zero

Often in PostgreSQL you may want to replace null values in a table with zeros instead.

You can use the COALESCE statement with the following syntax to do so:

SELECT athleteID,
       team,
       COALESCE(points, 0) AS points,
       COALESCE(assists, 0) AS assists
FROM athletes;

This particular example will replace the null values with zeros in both the points and assists columns of the table named athletes.

The following example shows how to use this syntax in practice.

Example: How to Replace Null Values with Zeros in PostgreSQL

Suppose that we create the following table named athletes that contains information about various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  id INTEGER PRIMARY KEY,
  player_info TEXT,
  points INTEGER,
  assists INTEGER
);

-- add rows to the table 
INSERT INTO athletes VALUES (0001, 'Mavs', NULL, 5);
INSERT INTO athletes VALUES (0002, 'Hawks', 30, 12);
INSERT INTO athletes VALUES (0003, 'Kings', 12, 3);
INSERT INTO athletes VALUES (0004, 'Kings', 25, 9);
INSERT INTO athletes VALUES (0005, 'Celtics', 10, 5);
INSERT INTO athletes VALUES (0006, 'Nuggets', NULL, NULL);
INSERT INTO athletes VALUES (0007, 'Nuggets', 20, 8);
INSERT INTO athletes VALUES (0008, 'Nuggets', NULL, 3);

-- view all rows in table
SELECT * FROM athletes;

 athleteid |  team   | points | assists 
-----------+---------+--------+---------
         1 | Mavs    |        |       5
         2 | Hawks   |     30 |      12
         3 | Kings   |     12 |       3
         4 | Kings   |     25 |       9
         5 | Celtics |     10 |       5
         6 | Nuggets |        |        
         7 | Nuggets |     20 |       8
         8 | Nuggets |        |       3

Notice that the points and assists columns both have several null values.

We can use the following syntax to replace the null values in each of these columns with zeros instead:

SELECT athleteID,
       team,
       COALESCE(points, 0) AS points,
       COALESCE(assists, 0) AS assists
FROM athletes;

 athleteid |  team   | points | assists 
-----------+---------+--------+---------
         1 | Mavs    |      0 |       5
         2 | Hawks   |     30 |      12
         3 | Kings   |     12 |       3
         4 | Kings   |     25 |       9
         5 | Celtics |     10 |       5
         6 | Nuggets |      0 |       0
         7 | Nuggets |     20 |       8
         8 | Nuggets |      0 |       3

Notice that each of the null values in the points and assists columns have been replaced with zeros.

Feel free to use similar syntax to replace null values in one or more columns of your own table.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Extract Substring Before Specific Character
PostgreSQL: How to Extract Substring After Specific Character
PostgreSQL: How to Extract Substring Between Two Characters