I'm looking to write a postgresql query to do the following :
if(field1 > 0, field2 / field1 , 0)
I've tried this query, but it's not working
if (field1 > 0)
then return field2 / field1 as field3
else return 0 as field3
thank youu
This question is related to
sql
postgresql
case when field1>0 then field2/field1 else 0 end as field3
In general, an alternative to case when ...
is coalesce(nullif(x,bad_value),y)
(that cannot be used in OP's case). For 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)
Source: Stackoverflow.com