Re: NUMERIC private methods? - Mailing list pgsql-hackers

Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, Dec 18, 2014 at 10:21 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> As the guy who last fooled with the numeric calculation algorithms in any
>> major way, I'm painfully aware that numeric is not necessarily more
>> accurate than double for anything more complicated than
>> addition/subtraction/multiplication.  The example that was shown upthread
>> is pretty nearly a textbook case of something where I'd not believe that
>> numeric offers any accuracy improvement without *very* careful
>> investigation.

> I think that's ridiculous.  You're basically arguing that numeric
> doesn't offer meaningful advantages over float8, which flies in the
> face of the fact that essentially every database application I've ever
> seen uses numeric and I'm not sure I've ever seen one using float8.
> Nearly all database users prefer to store quantities like currency
> units in a type that is guaranteed not to lose precision.

If you're doing banking, you don't do anything except addition,
subtraction, and multiplication.  And that is what those users
who want "guaranteed precision" are doing, and yeah numeric will
make them happy.

If you're doing any sort of higher math or statistics, I stand by my
statement that you'd better think rather than just blindly assume that
numeric is going to be better for you.  A moment's fooling about finds
this example, which is pretty relevant to the formula we started this
thread with:

regression=# select (1234::numeric/1235) * 1235;         ?column?          
---------------------------1234.00000000000000000100
(1 row)

regression=# select (1234::float8/1235) * 1235; ?column? 
----------    1234
(1 row)

What it boils down to is that numeric is great for storing given decimal
inputs exactly, and it can do exact addition/subtraction/multiplication
on those too, but as soon as you get into territory where the result is
fundamentally inexact it is *not* promised to be better than float8.
In fact, it's designed to be more or less the same as float8; see the
comments in select_div_scale.

We could probably improve on this if we were to redesign the algorithms
around a concept of decimal floating-point, rather than decimal
fixed-point as it is now.  But I'm not sure how well that would comport
with the SQL standard.  And I'm very not sure that we could still do it
once we'd tied one hand behind our backs by virtue of exporting a bunch
of the internals as public API.
        regards, tom lane