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

Improve this question
6
  • 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.
    – user1822
    Commented Jun 10, 2016 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ᵀᴹ
    Commented Jun 10, 2016 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ᵀᴹ
    Commented Jun 10, 2016 at 21:43
  • 5
    Necessary read: What Every Programmer Should Know About Floating-Point Arithmetic
    – ypercubeᵀᴹ
    Commented Jun 10, 2016 at 21:45
  • 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
    Commented Jun 10, 2016 at 23:05

1 Answer 1

Reset to default
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

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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