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?
Best Answer
You are falling victim of implicit type casts. When one of the operands is
numeric
and another oneinteger
, the integer operand will be cast tonumeric
and the result will benumeric
. Sincenumeric
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 precisionnumeric
33.333...3 (a finite sequence of "3"s), which naturally produces 99.999...9 -- a finite sequenceWhen one of the operands is
float
and another oneinteger
, the integer operand will be cast tofloat
and the result will befloat
. Sincefloat
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
the manual gives you the exact answer you need:
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.