Unexpected behavior with CASE statement

From: "Jimmy Choi" <yhjchoi(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Unexpected behavior with CASE statement
Date: 2007-10-03 19:05:14
Message-ID: 5770602b0710031205s1e81ff77h5a4eb23d806fa790@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Suppose I have the following table named "metrics":

metric_type | val
------------+-----
0 | 1
0 | 1
1 | 0
1 | 3

Now suppose I run the following simple query:

select
metric_type,
case metric_type
when 0 then
sum (1 / val)
when 1 then
sum (val)
end as result
from metrics
group by metric_type

I expect to get the following result set:

metric_type | result
------------+-------
0 | 2
1 | 3

But in reality I get the following error:

ERROR: division by zero
SQL state: 22012

So it appears that Postgres executes all cases and select the result
in the end. Is this expected behavior?

Thanks
- Jimmy

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Brian Wipf 2007-10-03 19:05:56 Re: PITR and Compressed WALS
Previous Message Richard Huxton 2007-10-03 19:03:09 Re: PITR Recovery and out-of-sync indexes