Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.200.121\))
Subject: Re: Inconsistent results for division and multiplication operations
From: Philip Semanchuk <philip@americanefficient.com>
In-Reply-To: 
 <CAFcck8EQ3h15-P=Ndt269gE50FDMc=BmWM0iVfUuLbev5HJUbQ@mail.gmail.com>
Date: Mon, 25 Nov 2024 11:23:36 -0500
Cc: szy <598546998@qq.com>,
 pgsql-sql <pgsql-sql@lists.postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <E37CBAEC-E665-48EB-A323-E1B0F1458E4F@americanefficient.com>
References: <tencent_59551086059D604A0E8C30A43A934D7D6906@qq.com>
 <CAFcck8EQ3h15-P=Ndt269gE50FDMc=BmWM0iVfUuLbev5HJUbQ@mail.gmail.com>
To: Erik Brandsberg <erik@heimdalldata.com>
Archived-At: 
 <https://www.postgresql.org/message-id/E37CBAEC-E665-48EB-A323-E1B0F1458E4F%40americanefficient.com>
Precedence: bulk


> On Nov 25, 2024, at 10:53=E2=80=AFAM, Erik Brandsberg =
<erik@heimdalldata.com> wrote:
>=20
> 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-calc=
ulations-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 =E2=80=9Cuse as necessary=E2=80=9D =
data type.

Hope this helps
Philip


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