Avoid division by zero in PostgreSQL

I'd like to perform division in a SELECT clause. When I join some tables and use aggregate function I often have either null or zero values as the dividers. As for now I only come up with this method of avoiding the division by zero and null values.

(CASE(COALESCE(COUNT(column_name),1)) WHEN 0 THEN 1
ELSE (COALESCE(COUNT(column_name),1)) END) 

I wonder if there is a better way of doing this?

1

6 Answers

You can use NULLIF function e.g.

something/NULLIF(column_name,0)

If the value of column_name is 0 - result of entire expression will be NULL

3

Since count() never returns NULL (unlike other aggregate functions), you only have to catch the 0 case (which is the only problematic case anyway). So, your query simplified:

CASE count(column_name) WHEN 0 THEN 1 ELSE count(column_name)
END

Or simpler, yet, with NULLIF(), like Yuriy provided.

Quoting the manual about aggregate functions:

It should be noted that except for count, these functions return a null value when no rows are selected.

0

I realize this is an old question, but another solution would be to make use of the greatest function:

greatest( count(column_name), 1 ) -- NULL and 0 are valid argument values

---

Note:My preference would be to either return a NULL, as in Erwin and Yuriy's answer, or to solve this logically by detecting the value is 0 before the division operation, and returning 0. Otherwise, the data may be misrepresented by using 1.

3

Another solution avoiding division by zero, replacing to 1

select column + (column = 0)::integer;

3

If you want the divider to be 1 when the count is zero:

count(column_name) + 1 * (count(column_name) = 0)::integer

The cast from true to integer is 1.

2

I did the below as suggested and it worked

column_name / COALESCE(NULLIF(column_name,0), 1)

Even a sum function can work like below:

 (
sum(column_name) :: decimal / COALESCE( NULLIF(sum(other_column_name) :: decimal, 0),1) :: decimal * 100
)

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.