5

Reading the Postgresql docs about the numeric data types leads me to this question: why do I get these unexpected results with the data types Float (SQL standard) and Numeric in Postgresql?

For example:

CREATE TEMP TABLE testnum (a numeric, b float);
INSERT INTO testnum VALUES (100,100);
INSERT INTO testnum VALUES (9*9*9,9*9*9);
INSERT INTO testnum VALUES (9^9^9,9^9^9);
SELECT (a/3)*3 AS numeric, (b/3) * 3 AS float FROM testnum;
SELECT (a/5)*5 AS numeric, (b/5) * 5 AS float FROM testnum;

Then run

SELECT (a/3)*3 AS numeric, (b/3) * 3 AS float FROM testnum;

numeric                                     |         float          
                        99.9999999999999999 |                    100
                       729.0000000000000000 |                    729

In this test Float looks more accurate than Numeric.

Which one is the appropriate data type for precise numeric values like currency or inventory quantities?

3
  • 2
    numeric is also an SQL standard data type.
    – mustaccio
    Oct 28, 2020 at 20:53
  • when you get such numbers t alsways caused by fractals.And tehre lies the basuc difference how the numeric and floar are stored, but that should be covert in the manual
    – nbk
    Oct 28, 2020 at 21:13
  • "Which one is the appropriate data type for precise numeric values" - clearly numeric. Related: floating-point-gui.de
    – user1822
    Oct 28, 2020 at 22:14

1 Answer 1

5

You are falling victim of implicit type casts. When one of the operands is numeric and another one integer, the integer operand will be cast to numeric and the result will be numeric. Since numeric has a strictly defined precision, attempting to assign to it a value with infinitely many digits after the decimal point (which 100/3 produces), without rounding, will result in truncation. The same logic applies to multiplication, to which one operand now is not 33.333... (with an infinite sequence of "3"s) that you seem to expect, but rather a defined precision numeric 33.333...3 (a finite sequence of "3"s), which naturally produces 99.999...9 -- a finite sequence

When one of the operands is float and another one integer, the integer operand will be cast to float and the result will be float. Since float has an approximate precision, it is equipped to deal with 33.333... and 99.999... differently, which eventually results in an approximate value of 100.

As to

Which one is the appropriate data type for precise numeric values like currency or inventory quantities?

the manual gives you the exact answer you need:

If you require exact storage and calculations (such as for monetary amounts), use the numeric type instead.

What it does not say is that you should control calculations at each step by using explicit type casting and rounding appropriate for your business rules.

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.