PostgreSql: using an operator as a conditional condition
when I use the statement as a 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 this condition?
source to share
CASE WHEN
expects the result boolean
from the condition according to the documentation :
Every condition is an expression that returns a boolean result.
Operators SELECT
return a relationship (yes, with one row having one column with type boolean
and value TRUE
, but that still isn't boolean
).
source to share
The subquery (select true)
returns a boolean value:
select (select true);
bool
------
t
And it case
works:
select (case when (select true) then 1 else 0 end);
case
------
1
The problem is that the expression is else
always evaluated:
select (case when (select true) then 1 else 1/0 end);
ERROR: division by zero
In instead, split the condition cast from the whole:
select 1/true::integer;
?column?
----------
1
select 1/false::integer;
ERROR: division by zero
Your example code:
select *
from table
order by id/(my_condition)::integer desc
By the way, are you sure you want to throw an exception?
source to share