Problem
I’m seeking for a postgresql query that will allow me to do the following:
if(field1 > 0, field2 / field1 , 0)
I tried this query, but it didn’t work for me.
if (field1 > 0)
then return field2 / field1 as field3
else return 0 as field3
thank youu
Asked by user2311028
Solution #1
As mentioned in the PostgreSQL documentation:
a snippet of code that particularly answers your question:
SELECT field1, field2,
CASE
WHEN field1>0 THEN field2/field1
ELSE 0
END
AS field3
FROM test
Answered by Joseph Victor Zammit
Solution #2
case when field1>0 then field2/field1 else 0 end as field3
Answered by Thanos Darkadakis
Solution #3
In general, coalesce(nullif(x,bad value),y) is an alternative to case when… (which cannot be used in the OP’s scenario). As an example,
select coalesce(nullif(y,''),x), coalesce(nullif(x,''),y), *
from ( (select 'abc' as x, '' as y)
union all (select 'def' as x, 'ghi' as y)
union all (select '' as x, 'jkl' as y)
union all (select null as x, 'mno' as y)
union all (select 'pqr' as x, null as y)
) q
gives:
coalesce | coalesce | x | y
----------+----------+-----+-----
abc | abc | abc |
ghi | def | def | ghi
jkl | jkl | | jkl
mno | mno | | mno
pqr | pqr | pqr |
(5 rows)
Answered by 18446744073709551615
Post is based on https://stackoverflow.com/questions/19029842/if-then-else-statements-in-postgresql