PostgreSql : using a statement as case condition

Question

when i use a statement as case condition it always returns false;

select * from table order by (case when (true) then id else 1/0 end)  desc -- works 
select * from table order by (case when (select true) then id else 1/0 end)  desc -- exception
select * from table order by (case when (1=1) then id else 1/0 end)  desc -- works
select * from table order by (case when (select 1=1) then id else 1/0 end)  desc -- exception

what is wrong with that condition?

Patrick · Accepted Answer · 2015-04-26 09:23:48Z

1

The CASE WHEN expects a boolean result from the condition as per the documentation:

Each condition is an expression that returns a boolean result.

The SELECT statements return a relation (yes, with a single row having a single column with a boolean type and a value of TRUE but that is still not a boolean).

answered Apr 26, 2015 at 9:23

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

idemirel Over a year ago

ok, is there any way to convert that reletion to boolean ! i tried to convert it with ::boolean operator. it doesn't work.

Clodoaldo Neto Over a year ago

Yes, the select statement returns a relation but the single returned value from a scalar subquery (select...) is used in the expression.

Clodoaldo Neto · Accepted Answer · 2015-04-26 11:45:46Z

1

The subquery (select true) returns a boolean value:

select (select true);
 bool 
------
 t

And the case works:

select (case when (select true) then 1 else 0 end);
 case 
------
    1

The problem is that the else expression is always evaluated:

select (case when (select true) then 1 else 1/0 end);
ERROR:  division by zero

In instead divide by the condition casted to integer:

select 1/true::integer;
 ?column? 
----------
        1

select 1/false::integer;
ERROR:  division by zero

Your sample code would be:

select *
from table
order by id/(my_condition)::integer desc

BTW are you sure you want to raise an exception?

edited Apr 26, 2015 at 11:45

answered Apr 26, 2015 at 11:36

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

3 Comments

user330315 Over a year ago

"the else expression is always evaluated" contradicts with the manual: "If the condition's result is true, the value of the CASE expression is the result that follows the condition, and the remainder of the CASE expression is not processed"

Clodoaldo Neto Over a year ago

@a_horse_with_no_name When the condition involves a subselect that is not true as shown in my example.

Clodoaldo Neto Over a year ago

@a_horse_with_no_name That wording is not clear. What is the remainder of the CASE expression? The other when conditions?

Collectives™ on Stack Overflow

PostgreSql : using a statement as case condition

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related