1

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?

  • 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)? – ypercubeᵀᴹ Jul 11 '18 at 20:19
  • @ypercubeᵀᴹ: yes, it says: DETAIL: Key ("position")=(150.832000) is duplicated. – Sébastien Clément Jul 11 '18 at 20:39
  • Try with more precision. Or just numeric. – ypercubeᵀᴹ Jul 11 '18 at 20:43
  • If all fail, you can create the table from scratch and load it from the file (it seems) you have. – ypercubeᵀᴹ Jul 11 '18 at 20:44
  • Does your SQL client limit the displayed number of decimals? – a_horse_with_no_name Jul 11 '18 at 22:09
0

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

0

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.
  • 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. – Sébastien Clément Jul 12 '18 at 12:57
  • 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... – Patrick Mevzek Jul 12 '18 at 13:29
  • decimal digits precision refers to precision AFTER the decimal point. – Sébastien Clément Jul 12 '18 at 13:47
  • 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... – Patrick Mevzek Jul 12 '18 at 14:58
  • 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. – Sébastien Clément Jul 17 '18 at 15:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.