Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iMdZs-0002wY-Fq for pgsql-bugs@arkaria.postgresql.org; Mon, 21 Oct 2019 19:42:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1iMdZr-000724-42 for pgsql-bugs@arkaria.postgresql.org; Mon, 21 Oct 2019 19:42:51 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iMdZq-00071x-ST for pgsql-bugs@lists.postgresql.org; Mon, 21 Oct 2019 19:42:50 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1iMdZo-0004Dy-MB for pgsql-bugs@lists.postgresql.org; Mon, 21 Oct 2019 19:42:50 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.4/8.14.4) with ESMTP id x9LJgbZ2005046; Mon, 21 Oct 2019 15:42:38 -0400 From: Tom Lane To: Gilleain Torrance cc: "pgsql-bugs@lists.postgresql.org" , Andrew Gierth Subject: Re: Incorrect rounding of double values at max precision In-reply-to: References: Comments: In-reply-to Gilleain Torrance message dated "Mon, 21 Oct 2019 11:29:04 -0000" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <5044.1571686957.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Mon, 21 Oct 2019 15:42:37 -0400 Message-ID: <5045.1571686957@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk Gilleain Torrance writes: > When storing a double in Postgres, it looks like under specific circumst= ances it can get rounded incorrectly: > select round(cast(float8 '42258656681.38498' as numeric), 2), round(nume= ric '42258656681.38498', 2); > which returns either 42258656681.38 or 42258656681.39 depending on wheth= er it is float8 or not. I think this is behaving as expected. float8-to-numeric conversion rounds the float8 to 15 (DBL_DIG) decimal places, since that's as much precision as you're guaranteed to have. So what comes out of the cast is regression=3D# select cast(float8 '42258656681.38498' as numeric); numeric = ----------------- 42258656681.385 (1 row) and then that rounds up to 42258656681.39. In the other case you have an exact numeric value of 42258656681.38498, so it's unsurprisingly rounded to 42258656681.38. You could quibble about whether numeric round() ought to apply round-up or round-to-nearest-even when dealing with exact halfway cases. If it did the latter, this particular case would match up, but other cases would not, so I don't think it's a helpful proposal for this issue. The other thing we could conceivably do is ask sprintf for more digits. But since those extra digit(s) aren't fully precise, I'm afraid that would likewise introduce as many oddities as it fixes. Still, it's somewhat interesting to wonder whether applying the Ryu algorithm would produce better or worse results on average. regards, tom lane