Wrong output getting from query

Question

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 ?

• user1822: Unrelated, but: the cast to ::character varying is completely useless
• Vérace: @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!

Answer 1

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

• Rick James: Or tack .0 on the end of those integers?
• Vérace: 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: @RickJames - the explicit typecast is much more legible and clearer than the adding a .0 fix - gotta love PostgreSQL's ::TYPE syntax for CASTs!
• Rick James: @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.
• Vérace: @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!