3

I can't seem to figure out how this is rounding overflow digits. It is most definitely not any of:

  • Round up if last digit is 5 or higher.
  • Round to even if last digit is 5.
  • Randomly round up or down if last digit is 5.

In fact, whatever logic it is using is definitely 1) deterministic, and 2) not [completely] based on the last digit being 5.

Some example inputs and output of:

CAST ('input' AS DOUBLE PRECISION);

Here, 'input' is not a variable, just a placeholder in this post for a literal numeric value with quotes around it, e.g. below in input column.

input                 output
34.55555555555595     34.5555555555559    *Obviously not rounding up
34.555555555555951    34.5555555555559    *Why not round up here?
34.5555555555559514   34.5555555555559    *Another unexpected round down
34.5555555555559515   34.555555555556     *Unexpected/seemingly inconsistent
34.55555555555595145  34.5555555555559
34.55555555555595146  34.5555555555559
34.55555555555595148  34.5555555555559
34.55555555555595149  34.555555555556     *Huh!?

Version: PostgreSQL 9.3.13 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-19ubuntu1) 4.8.2, 64-bit

  • 1
    CAST ('x' AS DOUBLE PRECISION); will not work at all. It will result in the error: invalid input syntax for type double precision: "x" and will not return any values at all. – a_horse_with_no_name Jun 10 '16 at 21:29
  • From Postgres docs: Numeric types: "double precision, 8 bytes, variable-precision, inexact, 15 decimal digits precision" You are passing more than 15, even 18 digits to a type that can hold 15-16 digits. What did you expect? – ypercubeᵀᴹ Jun 10 '16 at 21:40
  • From the same link: "Inexact means that some values cannot be converted exactly to the internal format and are stored as approximations, so that storing and retrieving a value might show slight discrepancies." – ypercubeᵀᴹ Jun 10 '16 at 21:43
  • 5
  • a_horse_with_no_name: I was using 'x' as a symbol within the post itself, not as a variable. I updated the question to be more clear. – Jim Jun 10 '16 at 23:05
1

Based on your last comment,

JavaScript parseFloat("34.55555555555595149").toPrecision(15)

the cast should be to the numeric datatype

CAST ('input' AS numeric(17,15))

https://www.postgresql.org/docs/8.1/static/datatype.html#DATATYPE-NUMERIC-DECIMAL

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.