Unexpected behavior with CASE statement

Поиск
Список
Период
Сортировка
От Jimmy Choi
Тема Unexpected behavior with CASE statement
Дата
Msg-id 5770602b0710031205s1e81ff77h5a4eb23d806fa790@mail.gmail.com
обсуждение исходный текст
Ответы Re: Unexpected behavior with CASE statement  ("Rodrigo De León" <rdeleonp@gmail.com>)
Re: Unexpected behavior with CASE statement  (Tom Lane <tgl@sss.pgh.pa.us>)
Список 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

В списке pgsql-general по дате отправления:

Предыдущее
От: Richard Huxton
Дата:
Сообщение: Re: PITR Recovery and out-of-sync indexes
Следующее
От: Brian Wipf
Дата:
Сообщение: Re: PITR and Compressed WALS