Using PostgreSQL 9.2 on Windows Server 2007, I have the following table:
CREATE TABLE aaa_dens (read_no INT, position REAL PRIMARY KEY);
Some values in position
have 4 or more digits after the decimal point, as shown from a dump:
$ head dump.txt
"read_no" "position"
"1" "150.83"
"2" "150.8308"
"3" "150.8316"
"4" "150.8324"
"5" "150.83321"
"6" "150.834"
"7" "150.83479"
"8" "150.8356"
"9" "150.8364"
However, SELECT * FROM aaa_dens;
shows only a max of 3 digits after the decimal point:
read_no | position
---------+----------
1 | 150.83
2 | 150.831
3 | 150.832
4 | 150.832
5 | 150.833
6 | 150.834
7 | 150.835
8 | 150.836
9 | 150.836
Furthermore, trying to convert position
to numeric(8,4)
yields an error, as if it had rounded reads 3 and 4 to 150.832:
ALTER TABLE aaa_dens ALTER COLUMN position TYPE NUMERIC(8,4);
ERROR: could not create unique index "pk_aaa_dens"
DÉTAIL : Key ("position")=(150.8320) is duplicated.
What exactly is happening here and how to fix it?
Best Answer
From https://www.postgresql.org/docs/9.2/static/datatype-numeric.html we read:
and precision is defined as such: "The precision of a numeric is the total count of significant digits in the whole number, that is, the number of digits to both sides of the decimal point" (same definition for all types, note how in the same table
decimal
andnumeric
have their precision defined separately from left and right of the decimal point)You do have below:
But that (more than 6 digits of precision) depends on both OS, processor, and compilation.
From your set, you have many numbers over 6 digits in precision, and unfortunately in your case you seem to have exactly only 6 digits available to you as precision, hence all these numbers are not handled as you wrote them:
You have this explicit warning in the documentation:
Which explains your observation as if it had rounded reads 3 and 4 to 150.832 since 3 is
150.8316
(rounds naturally to150.832
because the final 6 is more than 5) and150.8324
(rounds naturally to150.832
because the final 4 is less than 5)See also the bit about the
extra_float_digits
configuration item.You solve the problem going to double precision because in that case you have 15 digits of precision...
But the "real"(!) solutions are:
numeric(8,5)
right from the beginning.