How to Avoid Division by Zero in PostgreSQL (With Example)

If you attempt to divide a number by zero in PostgreSQL, a division by zero error will be thrown.

You can use the following syntax to avoid dividing by zero and thus avoid this error:

SELECT *,
       makes / GREATEST(attempts, 1) AS make_percentage
FROM athletes;

This particular example will divide the value in the makes column by the value in the attempts column.

If the value in the attempts column happens to be zero then we are able to avoid dividing by zero by dividing by the value 1 instead, which simply returns the value from the makes column.

Note that we use the GREATEST function to find the max value between the value in the attempts column and the number 1. If the value in the attempts column is equal to zero, then the GREATEST function returns 1.

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

Example: How to Avoid Division by Zero in PostgreSQL

Suppose that we create the following table named athletes that contains information about the total number of shot attempts and makes by various basketball players:

-- create table named 'athletes'
CREATE TABLE athletes (
  team TEXT,
  makes NUMERIC,
  attempts NUMERIC
);

-- add rows to the table 
INSERT INTO athletes VALUES ('Lakers', 7, 11);
INSERT INTO athletes VALUES ('Celtics', 2, 7);
INSERT INTO athletes VALUES ('Rockets', 10, 15);
INSERT INTO athletes VALUES ('Nuggets', 9, 17);
INSERT INTO athletes VALUES ('Warriors', 0, 0);
INSERT INTO athletes VALUES ('Kings', 1, 5);

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

   team   | makes | attempts 
----------+-------+----------
 Lakers   |     7 |       11
 Celtics  |     2 |        7
 Rockets  |    10 |       15
 Nuggets  |     9 |       17
 Warriors |     0 |        0
 Kings    |     1 |        5

Suppose that we would like to calculate the percentage of shots made for each athlete by dividing the number in the makes column by the number in the attempts column.

Suppose we use the following syntax to do so:

SELECT *,
       (makes / attempts) AS make_percentage
FROM athletes;

psql:commands.sql:22: ERROR:  division by zero

Notice that this returns the error division by zero since we attempted to divide by zero in one of the rows of the attempts column.

To avoid this error, we can use the following syntax instead:

SELECT *,
       makes / GREATEST(attempts, 1) AS make_percentage
FROM athletes;

   team   | makes | attempts |    make_percentage     
----------+-------+----------+------------------------
 Lakers   |     7 |       11 | 0.63636363636363636364
 Celtics  |     2 |        7 | 0.28571428571428571429
 Rockets  |    10 |       15 | 0.66666666666666666667
 Nuggets  |     9 |       17 | 0.52941176470588235294
 Warriors |     0 |        0 | 0.00000000000000000000
 Kings    |     1 |        5 | 0.20000000000000000000

Notice that we don’t receive an error this time.

The new column named make_percentage shows the percentage of attempts made by each basketball player.

Feel free to use similar syntax to avoid dividing by zero in your own table.

Additional Resources

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

PostgreSQL: How to Calculate a Moving Average
PostgreSQL: How to Round Numbers to 2 Decimal Places
PostgreSQL: Count Occurrences of Each Value in Column