Avoid division by zero in PostgreSQL
SqlPostgresqlNullAggregate FunctionsDivide by-ZeroSql Problem Overview
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?
Sql Solutions
Solution 1 - Sql
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
Solution 2 - Sql
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.
Solution 3 - Sql
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
.
Solution 4 - Sql
Another solution avoiding division by zero, replacing to 1
select column + (column = 0)::integer;
Solution 5 - Sql
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.