0

Hope someone can help! We're encountering an issue with the Pervasive VAccess control whereby any time we save an item of type 'double' into a Pervasive database, the value saved is different to the one we want...

As an example we try to save 1.44 and it actually saves 1.44004035454

A slight difference but a difference nonetheless!

FYI the field defined in the DDF has decimal set to 0, i'm wondering if one course of action is to set this to e.g. 4? But thought i'd see if anyone can shed any light on it before we head down that path...

Milen A. Radev
  • 60,241
  • 22
  • 105
  • 110
user3218601
  • 1

1 Answers1

0

The underlying effect is nothing to do with pervasive, it's a simple floating point issue. You'll find the same in any system that uses single- or double-precision floating point, though some systems do automatic rounding to hide this from you.

See http://en.wikipedia.org/wiki/Floating_point

In the case of PostgreSQL and its derivatives, you can set extra_float_digits to control this rounding.

regress=> SET extra_float_digits = 3;
SET
regress=> SELECT FLOAT8 '1.44';
       float8        
---------------------
 1.43999999999999995
(1 row)

regress=> SET extra_float_digits = 0;
SET
regress=> SELECT FLOAT8 '1.44';
 float8 
--------
   1.44
(1 row)

It defaults to 0, but your client driver might be changing it. If you're using JDBC (which I'm guessing you are) then don't mess with this setting, the JDBC driver expects it to remain how the driver sets it and will get upset at you if you change it.

In general, if you want a human-readable formatted number you should be doing the rounding with round or to_char, or doing it client-side, instead. Note that there's no round(double precision, integer) function for reasons explained in answers to this question. So you'll probably want to_char, eg.

regress=> SELECT to_char(FLOAT8 '1.44', 'MI999999999D99');
    to_char    
---------------
          1.44
(1 row)

(I wish PostgreSQL exposed a version of the cast from float8 to text that let you specify extra_float_digits on a per-call basis. That's often closer to what people really want. Guess I should add that if I get the time...)

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • Note the OP is using Pervasive database engine (http://en.wikipedia.org/wiki/Pervasive_PSQL) NOT Postgres – Matt Wilko Jan 29 '14 at 07:45
  • @MattWilko Yes, as I noted in the first par. For this purpose it wouldn't matter much if they were using Python, the general principle still applies. Floats are imprecise, so what you store isn't necessarily what you get back, and you should round and format them for user display. – Craig Ringer Jan 29 '14 at 08:09
  • Agree with all that my point was that `extra_float_digits` won't be recognised in Pervasive PSQL – Matt Wilko Jan 29 '14 at 08:15
  • @MattWilko So VAccess isn't Pervasive's PostgreSQL fork/derivative then? I figured it was based on the original tag. – Craig Ringer Jan 29 '14 at 08:19
  • VAccess is an ActiveX control (OCX) used to read/write to a Pervasive PSQL database - old skool but still used extensively as it is lightweight and extremely quick. I'm guessing the OP has a VB6 app or possibly .NET using Interop – Matt Wilko Jan 29 '14 at 08:27