Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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
                look at "conditional expressions" (CASE) postgresql.org/docs/current/static/functions-conditional.html
– Paolo Falabella
                Sep 26, 2013 at 13:37
  
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,
    WHEN field1>0 THEN field2/field1
    ELSE 0
  AS field3
FROM test
                Out of curiosity, wasn't there any solution using if-then-else statement? The question asked for an if-then-else but the answer is a switch-case statement.
– Abel Callejo
                Dec 19, 2016 at 1:14
                Hi Abel, the question asked for the solution to a specific problem. The answer addresses that specific problem. Within SELECT statements the conditionals you can use, of which one is CASE, are documented here.
– Joseph Victor Zammit
                Dec 19, 2016 at 10:45
                In that case, it will be more clear if you add in the answer the reason why is more difficult or why you can't achieve this using if-then the statement.
– Max Becerra
                Jul 26, 2017 at 6:44
                @JosephVictorZammit - I've been looking for that documentation on select statement conditionals for a couple days, but all I'd been getting was docs on conditionals inside functions.  I guess I didn't have the right magic google incantation until now, and your earlier comment was golden!  Thank you!
– Randall
                Feb 2 at 15:51
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)
gives:
 coalesce | coalesce |  x  |  y  
----------+----------+-----+-----
 abc      | abc      | abc | 
 ghi      | def      | def | ghi
 jkl      | jkl      |     | jkl
 mno      | mno      |     | mno
 pqr      | pqr      | pqr | 
(5 rows)
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.