CODEWITHSUNDEEP
sqlpostgresql
user2311028
user2311028

Reputation: 1701

IF-THEN-ELSE statements in postgresql

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

Upvotes: 159

Views: 384140

Answers (3)

18446744073709551615
18446744073709551615

Reputation: 16832

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)

Upvotes: 4

Joseph Victor Zammit
Joseph Victor Zammit

Reputation: 15310

As stated in PostgreSQL docs here:

The SQL CASE expression is a generic conditional expression, similar to if/else statements in other programming languages.

Code snippet specifically answering your question:

SELECT field1, field2,
  CASE
    WHEN field1>0 THEN field2/field1
    ELSE 0
  END 
  AS field3
FROM test

Upvotes: 281

Thanos Darkadakis
Thanos Darkadakis

Reputation: 1729

case when field1>0 then field2/field1 else 0 end as field3

Upvotes: 41

Related Questions