Unexpected behavior with CASE statement

From: "Jimmy Choi" <JCHOI(at)altera(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: Unexpected behavior with CASE statement
Date: 2007-10-03 18:29:57
Message-ID: 6E3775AF29598B46AA3F102067A510F102134F0F@tor-ismsg01.altera.priv.altera.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

Confidentiality Notice. This message may contain information that is confidential or otherwise protected from disclosure.
If you are not the intended recipient, you are hereby notified that any use, disclosure, dissemination, distribution,
or copying of this message, or any attachments, is strictly prohibited. If you have received this message in error,
please advise the sender by reply e-mail, and delete the message and any attachments. Thank you.

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2007-10-03 18:32:08 Re: PITR Recovery and out-of-sync indexes
Previous Message Erik Jones 2007-10-03 18:16:48 Re: Generating TRUNCATE orders