Avoid division by zero in PostgreSQL
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
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)ENDOr simpler, yet, with NULLIF(), like Yuriy provided.
Quoting the manual about aggregate functions:
It should be noted that except for
count, these functions return anull value when no rows are selected.
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 valuesNote: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.