Why does Postgres select return wrong floating point calculation?

Question

Why does SELECT 2 * 4 * ( 5 / 2) * 4 return 64 instead of 80?

The real query I'm running looks like this:

SELECT 2 * equity_gains_score * (tenure_score / 2) * investor_score AS propensity

but I had the same result when I ran it substituting numbers for columns. How can I fix this statement?

Gordon Linoff · Accepted Answer · 2020-03-29 19:13:16Z

2

Postgres does integer division. So, 5/2 is 2 rather than 2.5.

You can just add a decimal point to one of the operands of the division:

SELECT 2 * 4 * ( 5 / 2.0) * 4

Or convert a value to a numeric:

SELECT 2 * 4 * ( 5::numeric / 2.0) * 4

Note: If you want an integer as the result, then you need to convert back to an integer:

SELECT (2 * 4 * ( 5 / 2.0) * 4)::int

answered Mar 29, 2020 at 19:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Answer 1