[SOLVED] Avoid division by zero in PostgreSQL

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?

Solution

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.