If statement inside select clause (postgresql)

Question

I want to set 0 or 1 flag based on column value.if the value is > 0.0 AND <= 99.0 then 1 ,else 0 .

              Score         flag_score

              0.083642      1
              0.009542      1
              0.999999      1
              101.0000      0

select score Score, case when score > 0.0 AND score <= 99.0 then 1 else 0 end flag_score from table; — Hunter McMillen
– Hunter McMillen, Commented May 11, 2018 at 18:36

Yogesh Sharma · Accepted Answer · 2018-05-11 18:31:57Z

3

Have you tried case expression ?

select *, 
        (case when value > 0.0 and value <= 99.0 then 1 else 0 end) flag_score
from table;

answered May 11, 2018 at 18:31

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user8545255 Over a year ago

Case statement works..But cant we write if statement for the above case

Abelisto Over a year ago

@user8545255 No. There is no if statement in the SQL standard.

sticky bit Over a year ago

@user8545255: You could write your own if() function if it'd made you happy.

Gordon Linoff · Accepted Answer · 2018-05-11 19:11:37Z

1

I would write this simply as:

select t.*, ( (value > 0.0) and (value <= 99.0) )::int as flag_score
from t;

Postgres has a nice shorthand for declaring flags, so case expressions are not needed.

If you want to actually set the value, you can do:

update t
    set flag = ( (value > 0.0) and (value <= 99.0) )::int;

answered May 11, 2018 at 19:11

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

If statement inside select clause (postgresql)

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related