Thread
-
Inconsistent results for division and multiplication operations
szy <598546998@qq.com> — 2024-11-25T15:46:28Z
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
-
Re: Inconsistent results for division and multiplication operations
Erik Brandsberg <erik@heimdalldata.com> — 2024-11-25T15:53:38Z
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 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 > > <https://wx.mail.qq.com/home/index?t=readmail_businesscard_midpage&nocheck=true&name=szy&icon=http%3A%2F%2Fthirdqq.qlogo.cn%2Fg%3Fb%3Doidb%26k%3DiaAmEopniaNALwVq2rar6n3Q%26kti%3DZLTe1AAAAAE%26s%3D640%26t%3D1622504424&mail=598546998%40qq.com&code=Gezi7Gl9e-YYAktdETJ0hu2-JIM5l3FTObsX105iXnV2Az4OVWt5ENk8nrBlqhIKcNOvXiaTQhfswEOxGaCYhQ> > >
-
回复： Inconsistent results for division and multiplication operations
szy <598546998@qq.com> — 2024-11-25T16:12:15Z
If the number of significant digits in the input is not fixed, it becomes challenging to achieve consistent results by rounding. for example postgres=# select round(1.003/1.002*5.01,2); ?column? -------------------------- 5.01 (1 row) postgres=# select round(1.003*5.01/1.002,2); ?column? -------------------- 5.02 (1 row) szy 598546998@qq.com 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 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
-
Re: Inconsistent results for division and multiplication operations
Philip Semanchuk <philip@americanefficient.com> — 2024-11-25T16:23:36Z
> 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, 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 <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 >
-
Re: Inconsistent results for division and multiplication operations
Tom Lane <tgl@sss.pgh.pa.us> — 2024-11-25T16:34:38Z
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
-
Re: Inconsistent results for division and multiplication operations
Martin Norbäck Olivers <martin@norpan.org> — 2024-11-26T08:13:22Z
On Mon, Nov 25, 2024 at 5:18 PM szy <598546998@qq.com> wrote: > If the number of significant digits in the input is not fixed, it becomes > challenging to achieve consistent results by rounding. > for example > postgres=# select round(1.003/1.002*5.01,2); > ?column? > -------------------------- > 5.01 > (1 row) > > postgres=# select round(1.003*5.01/1.002,2); > ?column? > -------------------- > 5.02 > (1 row) > > Correct. That's why you should always use numeric with the desired precision if you want precision numbers. for instance select 1.003/1.002*5.01 :: numeric(10,4) will give the same result as select 1.003*5.01/1.002 :: numeric(10,4) They are much slower to calculate than floating point, however, so if you don't care about precision you can keep using just floating point. Regards, Martin -- Martin Norbäck Olivers IT-konsult, Masara AB Telefon: +46 703 22 70 12 E-post: martin@norpan.org Kärrhöksvägen 4 656 72 Skattkärr