Postgresql - CASE/WHEN with wrong return

Question

I tried to use CASE/WHEN inside Postgresql to check two colums, but the results are odd. As it's shown in the image below, all the lines where "gain_value" is 8 AND "patrimony_value" have a higher value return a wrong result.

This is my statement:

    select stop_value, gain_value, patrimony_value,
case
    when patrimony_value >= gain_value then 1
    else 2
end
from copy.copy_stop_gain csg

Since it's a pretty straightforwad "if/else", i'm really not sure what i could be doing wrong. Can anyone show me where is my mistake?

Sample data is better presented as formatted text. See here for some tips on how to create nice looking tables. — user330315
– user330315, Commented Jan 10, 2020 at 15:24
I see that your column types are texts. Comparing text "8" with "103" is not same as number 8 with 103... — Lubo
– Lubo, Commented Jan 10, 2020 at 15:24

Lubo · Accepted Answer · 2020-01-10 15:26:47Z

2

Try casting string values to numbers (or perhaps change column type in schema)...

   select stop_value, gain_value, patrimony_value,
case
    when patrimony_value::INTEGER >= gain_value::INTEGER then 1
    else 2
end
from copy.copy_stop_gain csg

answered Jan 10, 2020 at 15:26

Lubo

1,86716 silver badges41 bronze badges

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

2 Comments

Pablo Fernandes Over a year ago

This is indeed my mistake, column types are text. Casting worked. Thank you!!

Belayer Over a year ago

You will still be much better off changing the type to a numeric type. While casting works when the data are proper numeric values eventually a non-numeric character will get into it, then your script aborts. Unless that column can legitimately contain non-numeric values in which case you need to validate for numeric values before casting. Storing numeric values is always a bad idea and always causes eventual problems.

Collectives™ on Stack Overflow

Postgresql - CASE/WHEN with wrong return

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related