0

Hi I have written this query as per my project requirement. But it is generating wrong output.

select (to_char((33)/nullif((17400/3600),0),'FM99,999,999'))::character varying as userdatas

currently it is generating 8 as output. But according to calculation it is different,

= 33/(17400/3600)

= 33/(4.8333333333333)

= 33/4.8333333333333

= 6.8275862068966

It should display 7

Can anyone help me with this issue ?

2
  • Unrelated, but: the cast to ::character varying is completely useless
    – user1822
    Jul 10, 2020 at 22:14
  • @a_horse_with_no_name not quite useless m'lud! Check out here - the bottom two fiddles - one gives a result of type TEXT and the other a VARCHAR type - a fine distinction indeed, but a distinction nonetheless!
    – Vérace
    Jul 11, 2020 at 4:46

1 Answer 1

0

All your numbers are integers, so the whole expression is done using integer division. You need to either provide numeric constant, e.g. using 3600.0 or cast at least one number to numeric

select 33/(17400/3600::numeric);

returns 6.8276, when applying to_char() it's correctly rounded to 7:

select to_char(33/(17400/3600::numeric),'FM99,999,999')

Online example

6
  • Or tack .0 on the end of those integers?
    – Rick James
    Jul 10, 2020 at 23:22
  • Could you explain why putting 33.0 doesn't work correctly whereas putting 3600.0 does work (see fiddle) - I find that puzzling?
    – Vérace
    Jul 11, 2020 at 4:50
  • @RickJames - the explicit typecast is much more legible and clearer than the adding a .0 fix - gotta love PostgreSQL's ::TYPE syntax for CASTs!
    – Vérace
    Jul 11, 2020 at 4:53
  • @Vérace - I would expect 33.0/(17400/3600) to do (17400/3600) in integer, then switch to float to do the rest. 33.0/(17400.0/3600.0) is what I was suggesting. It's a common technique in many (not all) languages.
    – Rick James
    Jul 11, 2020 at 6:08
  • @RickJames - thanks so much for that explanation - really clarified the whole area for me (which I thought that I had understood!). This fiddle shows the various permuations that are possible - evaluation order being critical - though a belt and braces approach is obviously best with .0 being tacked on everywhere - having ::NUMERIC everywhere would be a bit clumsy - thanks again!
    – Vérace
    Jul 11, 2020 at 6:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.