public inbox for [email protected]  

help / color / mirror / Atom feed

From: Philip Semanchuk <[email protected]>
To: Erik Brandsberg <[email protected]>
Cc: szy <[email protected]>
Cc: pgsql-sql <[email protected]>
Subject: Re: Inconsistent results for division and multiplication operations
Date: Mon, 25 Nov 2024 11:23:36 -0500
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAFcck8EQ3h15-P=Ndt269gE50FDMc=BmWM0iVfUuLbev5HJUbQ@mail.gmail.com>
References: <[email protected]>
	<CAFcck8EQ3h15-P=Ndt269gE50FDMc=BmWM0iVfUuLbev5HJUbQ@mail.gmail.com>



> On Nov 25, 2024, at 10:53 AM, Erik Brandsberg <[email protected]> wrote:
> 
> 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

@szy Erik is right on target. The fine details of floating point math confuse almost everyone at first encounter. If it makes you 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 type in Postgres). Math  operations are much faster on floating point than on fixed precision, so floating point is the default data 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 <[email protected]> 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
> [email protected]
>  

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Inconsistent results for division and multiplication operations
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox