From: | "Peter J(dot) Holzer" <hjp-pgsql(at)hjp(dot)at> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: How to do faster DML |
Date: | 2024-02-17 20:52:33 |
Message-ID: | 20240217205233.nvzdigbxgfq6dyfh@hjp.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 2024-02-16 01:34:01 +0100, Peter J. Holzer wrote:
> On 2024-02-15 16:51:56 -0700, David G. Johnston wrote:
> > What you see with an exact type is what you get, which allows for implementing
> > equality, unlike inexact which requires epsilon checking.
>
> You can check binary fp values for equality. If they are equal, they
> will compare equal. If they aren't, they won't.
>
> What you can't expect is that the laws of commutativity, associativity,
> etc. hold. If you compute a value in two different ways which should be
> equivalent mathematically (e.g. a*b/c vs. a/c*b), the result may be
> different and an equality test may fail. But that is also the case for
> numeric (and of course integer).
To illustrate that point:
hjp=> create table t_n (a numeric, b numeric, c numeric);
CREATE TABLE
hjp=> insert into t_n values(47, 52, 10);
INSERT 0 1
-- the numbers are not specially chosen. I just invoked
-- select (random()*100)::int;
-- three times, and they were the ones that came up.
hjp=> select *, a / b * c, a * c / b, a / b * c = a * c / b from t_n;
╔════╤════╤════╤════════════════════════╤════════════════════╤══════════╗
║ a │ b │ c │ ?column? │ ?column? │ ?column? ║
╟────┼────┼────┼────────────────────────┼────────────────────┼──────────╢
║ 47 │ 52 │ 10 │ 9.03846153846153846150 │ 9.0384615384615385 │ f ║
╚════╧════╧════╧════════════════════════╧════════════════════╧══════════╝
(1 row)
So with type numeric two expressions which should be equal
mathematically, aren't in fact equal.
Now let's try the same thing with binary floating point:
hjp=> create table t_f (a float8, b float8, c float8);
CREATE TABLE
hjp=> insert into t_f values(47, 52, 10);
INSERT 0 1
hjp=> select *, a / b * c, a * c / b, a / b * c = a * c / b from t_f;
╔════╤════╤════╤═══════════════════╤═══════════════════╤══════════╗
║ a │ b │ c │ ?column? │ ?column? │ ?column? ║
╟────┼────┼────┼───────────────────┼───────────────────┼──────────╢
║ 47 │ 52 │ 10 │ 9.038461538461538 │ 9.038461538461538 │ t ║
╚════╧════╧════╧═══════════════════╧═══════════════════╧══════════╝
(1 row)
Now they are indeed equal. This is *not* guaranteed and I got a bit
lucky here, but the fact that I got lucky on the first try shows that
"float bad, numeric good" is not backed up by reality.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
From | Date | Subject | |
---|---|---|---|
Next Message | Lok P | 2024-02-17 21:50:20 | Users and object privileges maintenance |
Previous Message | Kerr Livingstone | 2024-02-17 19:55:20 | Re: Version 6 binaries for RHEL 7 |