| 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: | Whole Thread | Raw Message | 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
| 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 |