Coder Perfect

Postgresql IF-THEN-ELSE statements


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,
    WHEN field1>0 THEN field2/field1
    ELSE 0
  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


 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