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?

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.

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