Let’s say you’re working with Postgresql (applicable to other DBMSs as well), and you come across the following error when trying to execute a select query involving division:

ERROR: division by zero

You would think Postgresql would play nice, and set all the invalid fields to NULL or 0, but instead it chooses to spit out an error and die.

The solution is to use the CASE conditional expression, which is very similar to an IF/ELSE statement in other languages. Here’s the general syntax:

CASE WHEN condition THEN result
     [WHEN ...]
     [ELSE result]
END

Here’s how you would use it in a SELECT statement:

SELECT a,
       CASE WHEN a=1 THEN 'one'
            WHEN a=2 THEN 'two'
            ELSE 'other'
       END
    FROM test;

I ran into this issue when I was trying to run a query that was similar to this one in structure:

SELECT log(
   (SELECT CAST(
      (SELECT COUNT(*) FROM table1 WHERE BLAH BLAH)
      AS double precision))
   /
   (SELECT CAST(
      (SELECT COUNT(*) FROM table2 WHERE BLAHBLAH)
      AS double precision)))

A possible (though probably not optimal) solution to this would be to wrap an additional SELECT and CASE around the query, ensuring that the denominator is not zero, and returning a default value if it is.

SELECT
		CASE WHEN (SELECT CAST(
                  (SELECT COUNT(*) FROM table2 WHERE BLAHBLAH)
                  AS double precision)) = 0
			THEN 0
			ELSE (SELECT log(
                  (SELECT CAST(
                     (SELECT COUNT(*) FROM table1 WHERE BLAH BLAH)
                     AS double precision))
                  /
                  (SELECT CAST(
                     (SELECT COUNT(*) FROM table2 WHERE BLAHBLAH)
                     AS double precision))))
		END

If you guys have other, more optimal solutions to the divide by zero issue, leave a comment and let me know.