Thread: Inconsistent results for division and multiplication operations

> On Nov 25, 2024, at 10:53 AM, Erik Brandsberg <erik@heimdalldata.com> wrote:
>
> This is a common issue with using floating point math.  You will see the same issue with many systems.  Basically,
theorder of operations can trigger very minor differences in results, but if you round the first result to the same
numberof significant digits as the input, it would be identical.
https://learn.microsoft.com/en-us/office/troubleshoot/access/floating-calculations-info

@szy Erik is right on target. The fine details of floating point math confuse almost everyone at first encounter. If it
makesyou feel any better, Python (and IIUC any other language that uses IEEE floating point notation) exhibits the same
quirk- 

$ python
>>> 1.003/1.002*5.01
5.015
>>> 1.003*5.01/1.002
5.014999999999999

If you need extremely accurate representation of numbers (e.g. for tracking money), use fixed precision (the numeric
typein Postgres). Math  operations are much faster on floating point than on fixed precision, so floating point is the
defaultdata type for non-integral values. Fixed precision is more of a “use as necessary” data type. 

Hope this helps
Philip




> On Mon, Nov 25, 2024 at 10:46 AM szy <598546998@qq.com> wrote:
>
> Hi PostgreSQL community,
>
> I have observed inconsistent results when performing division and multiplication operations in PostgreSQL.
>
> postgres=# select 1.003/1.002*5.01;
>          ?column?
> --------------------------
>  5.0149999999999999999806
> (1 row)
>
> postgres=# select 1.003*5.01/1.002;
>       ?column?
> --------------------
>  5.0150000000000000
> (1 row)
>
> However, the expected result should be consistent for both queries. The actual results differ
>
>
> szy
> 598546998@qq.com
>

Erik Brandsberg <erik@heimdalldata.com> writes:
> This is a common issue with using floating point math.  You will see the
> same issue with many systems.  Basically, the order of operations can
> trigger very minor differences in results, but if you round the first
> result to the same number of significant digits as the input, it would be
> identical.
> https://learn.microsoft.com/en-us/office/troubleshoot/access/floating-calculations-info

Yeah.  The OP is actually working with PG's "numeric" type, not
floating-point, but the principle is the same.  Some division
results can't be represented exactly in any finite number of
digits, so you get roundoff error.

            regards, tom lane