PostgreSQL truncating values of real numeric type

Question

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?

• ypercubeᵀᴹ: It should have been numeric from start, not real. Do you get the same error if you try to change it into numeric(10,6)?
• Sébastien Clément: @ypercubeTM: yes, it says: DETAIL: Key ("position")=(150.832000) is duplicated.
• ypercubeᵀᴹ: Try with more precision. Or just numeric.
• ypercubeᵀᴹ: If all fail, you can create the table from scratch and load it from the file (it seems) you have.
• user1822: Does your SQL client limit the displayed number of decimals?

Answer 1

From https://www.postgresql.org/docs/9.2/static/datatype-numeric.html we read:

Name Storage Size Description Range
real 4 bytes variable-precision, inexact 6 decimal digits precision

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 and numeric have their precision defined separately from left and right of the decimal point)

You do have below:

On most platforms, the real type has a range of at least 1E-37 to 1E+37 with a precision of at least 6 decimal digits.

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:

"2" "150.8308"
"3" "150.8316"
"4" "150.8324"
"5" "150.83321"
"7" "150.83479"
"8" "150.8356"
"9" "150.8364"

You have this explicit warning in the documentation:

Rounding might take place if the precision of an input number is too high.

Which explains your observation as if it had rounded reads 3 and 4 to 150.832 since 3 is 150.8316 (rounds naturally to 150.832 because the final 6 is more than 5) and 150.8324 (rounds naturally to 150.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:

1. if possible, upgrade your PostgreSQL as 9.2 is not supported anymore,
2. and more specific to your problem, use appropriate data types, if you need fixed precision for your data set you need at least numeric(8,5) right from the beginning.

• Sébastien Clément: Great thanks @Patrick Mevzek for the comprehensive explanation and suggestions. Still, I'm puzzled by the fact that the REAL type truncates to 3 decimal digits, when in its description it states that it keeps "6 decimal digits precision", that is, decimals after the decimal point, not the total decimals in the number in this case.
• Patrick Mevzek: No, precision is total number of digits both left and right part of the decimal point. You have 3 digits before the decimal point so that leaves only 3 after...
• Sébastien Clément: decimal digits precision refers to precision AFTER the decimal point.
• Patrick Mevzek: See the quote in my answer, it specifically says all digits, both after and before. Which is in line with your observation per the 6 digits limit for real. But now if you want to argue directly with PostgreSQL documentation, it will be better to discuss with them directly...
• Sébastien Clément: I think you shoud read again your own quote: "The precision of a numeric". It doesn't relate in any way to the real type. The real type specifies "6 decimal digits precision", meaning 6 digits at the right of the decimal point. If you are still unconvinced, please have a look a the definition of a decimal digit.

Answer 2

Hard to understand why, but converting to DOUBLE PRECISION before converting to NUMERIC(8,4) works:

SELECT read_no,position,position::numeric(8,4) AS pn,position::DOUBLE PRECISION AS pdp,position::DOUBLE PRECISION::NUMERIC(8,4) AS pdpn FROM aaa_dens LIMIT 10;
 read_no | position | pn | pdp | pdpn
---------+----------+----------+------------------+----------
 1 | 150.83 | 150.8300 | 150.830001831055 | 150.8300
 2 | 150.831 | 150.8310 | 150.830795288086 | 150.8308
 3 | 150.832 | 150.8320 | 150.831604003906 | 150.8316
 4 | 150.832 | 150.8320 | 150.832397460938 | 150.8324
 5 | 150.833 | 150.8330 | 150.833206176758 | 150.8332
 6 | 150.834 | 150.8340 | 150.833999633789 | 150.8340
 7 | 150.835 | 150.8350 | 150.83479309082 | 150.8348
 8 | 150.836 | 150.8360 | 150.835601806641 | 150.8356
 9 | 150.836 | 150.8360 | 150.836395263672 | 150.8364
 10 | 150.837 | 150.8370 | 150.837203979492 | 150.8372

Where: position = original value, truncated to 3 or less decimals by pg for unknown reasons

pn = conversion to numeric(8,4) - only 3 significant decimals kept

pdb = conversion to double precision - all decimals restored

pdpn = conversion to double precision, then numeric(8,4) - 4 significant decimals kept, as wanted