Posgtres CASE clause with SUM aggregation does not require ELSE
According to Postgres documentation :
The CASE expression does not evaluate any subexpressions that are not necessary to determine the result. For example, this is a possible way to avoid giving up division by zero:
SELECT ... WHERE CASE WHEN x <> 0 THEN y / x> 1.5 ELSE false END;
Why does the following expression return ERROR: division by zero?
- apparently evaluating the else part:
SELECT CASE WHEN SUM(0) = 0 THEN 42 ELSE 43 / 0 END
and
SELECT CASE WHEN SUM(0) = 0 THEN 42 ELSE 43 END
returns 42.
EDIT: So the above example doesn't work because Postgres calculates the immutable values (43/0) already at the planning stage. Our actual request looks something like this:
case when sum( column1 ) = 0
then 0
else round( sum( price
* hours
/ column1 ), 2 )
Although this query does not appear to be immutable (depending on the actual values), there is still division by zero error. Of course, the sum (column 1) is actually 0 in our case.
source to share
An interesting example. This has a good explanation. Let's say you have data like this:
db=# table test;
column1 | price | hours
---------+-------+-------
1 | 2 | 3
3 | 2 | 1
PostgreSQL executes your SELECT in two passes, first it calculates all aggregate functions (for example sum()
):
db=# select sum(column1) as sum1, sum(price * hours / column1) as sum2 from test;
sum1 | sum2
------+------
4 | 6
And then it will insert those results into your final expression and calculate the actual result:
db=# with temp as (
db(# select sum(column1) as sum1, sum(price * hours / column1) as sum2 from test
db(# ) select case when sum1 = 0 then 0 else round(sum2, 2) end from temp;
round
-------
6.00
It is now clear if there is an error in the first pass of the aggregate, it never reaches the CASE statement.
So this is not a problem in the documentation about the CASE statement - it applies to all conditionals, but about how aggregates are handled in the SELECT statement. This problem cannot occur in any other context, since aggregates are only allowed in SELECTs.
But in this case, the documentation needs updating. In this case, the correct documentation is " general SELECT processing ". Step # 4 talks about the GROUP BY and HAVING clauses, but in fact it also evaluates any aggregate functions in this step, regardless of GROUP BY / HAVING. And your CASE statement is evaluated in step 5.
Decision
A general solution, if you want to ignore aggregate inputs that would otherwise cause division by zero, use the nullif () construct to turn them into NULL:
round( sum( price * hours / nullif(column1, 0) ), 2 )
PostgreSQL 9.4 will introduce a new FILTER clause for aggregates that can also be used for this purpose:
round( sum( price
* hours
/ column1
) filter (where column1!=0), 2 )
source to share