CODEWITHSUNDEEP
sqlpostgresqlnullaggregate-functionsdivide-by-zero
William Wino
William Wino

Reputation: 3819

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?

Upvotes: 156

Views: 173180

Answers (6)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656221

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 shorter:

GREATEST(count(column_name), 1)

Or (especially to use null instead) with NULLIF(), like Yuriy provided.

The manual about aggregate functions:

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

Upvotes: 71

Cyber
Cyber

Reputation: 2704

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
)

Upvotes: 0

suff trek
suff trek

Reputation: 39767

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

Upvotes: 396

Manvel
Manvel

Reputation: 808

Another solution avoiding division by zero, replacing to 1

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

Upvotes: 9

vol7ron
vol7ron

Reputation: 42095

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.

Upvotes: 62

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

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.

Upvotes: 3

Related Questions