Avoid division by zero in PostgreSQL

sql postgresql null aggregate-functions divide-by-zero
William Wino picture William Wino · Jul 16, 2013 · Viewed 74.4k times · Source

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?

Answer

Yuriy Galanter picture Yuriy Galanter · Jul 16, 2013

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

Related questions

Best way to check for "empty or null value"

What is best way to check if value is null or empty string in Postgres sql statements? Value can be long expression so it is preferable that it is written only once in check. Currently I'm using: coalesce( trim(stringexpression),…

Read More
Select rows which are not present in other table

I've got two postgresql tables: table name column names ----------- ------------------------ login_log ip | etc. ip_location ip | location | hostname | etc. I want to get every IP address from login_log which doesn't have a row in ip_location. I …

Read More
IN Clause with NULL or IS NULL

Postgres is the database Can I use a NULL value for a IN clause? example: SELECT * FROM tbl_name WHERE id_field IN ('value1', 'value2', 'value3', NULL) I want to limit to these four values. I have …

Read More