Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1quITR-00HVZE-La for pgsql-general@arkaria.postgresql.org; Sat, 21 Oct 2023 20:21:29 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1quITO-006YXF-Jr for pgsql-general@arkaria.postgresql.org; Sat, 21 Oct 2023 20:21:27 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1quITO-006YX5-8x for pgsql-general@lists.postgresql.org; Sat, 21 Oct 2023 20:21:26 +0000 Received: from mail.hjp.at ([212.17.106.138] helo=rorschach.hjp.at) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1quITI-001dVG-KO for pgsql-general@lists.postgresql.org; Sat, 21 Oct 2023 20:21:25 +0000 Received: by rorschach.hjp.at (Postfix, from userid 1000) id 8ACCA1873C; Sat, 21 Oct 2023 22:21:17 +0200 (CEST) Date: Sat, 21 Oct 2023 22:21:17 +0200 From: "Peter J. Holzer" To: pgsql-general@lists.postgresql.org Subject: Re: How can I accurately infer the output of a SELECT statement FROM a table with floating-point data type? Message-ID: <20231021202117.75dmanjjnfhapyxd@hjp.at> Mail-Followup-To: pgsql-general@lists.postgresql.org References: <10b02a08.28b4.18b460a2f09.Coremail.krave@163.com> <3259196.1697688771@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="vzjspref3jc5jdwj" Content-Disposition: inline In-Reply-To: <3259196.1697688771@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --vzjspref3jc5jdwj Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable On 2023-10-19 00:12:51 -0400, Tom Lane wrote: > =3D?GBK?B?w6vDqw=3D=3D?=3D writes: > > Do we have a rule by follow which one can accurately info the output of= a SELECT statment FROM a table with floating-point data type? >=20 > The goal of our floating-point output functions is to produce > the shortest representation from which the stored value could > be recovered exactly. It's not unusual for that to require > two or three decimal digits more than the nominal precision. To illustrate this: A real has 24 bits of mantissa. 12345.123456789 is between 2**13 and 2**14, so there are 10 bits left for the fraction. Or in other words, the number must be approximated as a multiple of 1/1024. The closest we can get is 12345+124/1024 =3D 12345.123046875 12345.123 would obviously be rounded to the same number, so it's close enough and additional digits aren't necessary. But 12345.12 would be rounded to 12345+123/1024 =3D 12345.1201171875. That's different, so 7 digits are not enough in this case. hp --=20 _ | Peter J. Holzer | Story must make more sense than reality. |_|_) | | | | | hjp@hjp.at | -- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" --vzjspref3jc5jdwj Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQIzBAABCgAdFiEETtJbRjyPwVTYGJ5k8g5IURL+KF0FAmU0MrcACgkQ8g5IURL+ KF2FJw//Ux+y8gZVYfLysqLQUcs9hg9CagRj0Zr5x/dPzprL8LghwUXIKabAMDHF Mo7KCl3+9NzKRDO2X2p07I2zTTxM0NriDwVsNhU1lnJSSBHQJdE+IcX6ESnvph6m n+1jBaMuIlnyihwgpHGI9gj35KprRhC4AuMrA5Nrlo2N143gL1YiFbFK6V5WfDhR lLA7w2SPncBaxAWnVSKH/1cjUcNk7nF1IwKCDyT5JGqj3LjNw9L7Srv+XnU4zRVw tSE9QgkAEd3YXzRiHzO1WAmTiyWi5haKEP8n0Vpz8b98Zipnt/7AAdtM9tXVDl24 m2/8jcWmXWz7VvG0jF6mkKoDXrHDt0yPWnnkrwZh6YOJIC7Y4MCl4xuHmQhKH5q6 epH5Yh+5yXlJK5ouvUltQe0dVuJGMXvq6jxceeMIB9mpLD9lG3kpiftIEi4dr+wn PM/hU9a73u4gpgToMc3oNGvwYD985AWnBAMojyy5iMrobdXveewlk1oRV8Q6AZgn DPr8BEqsKmPWROWtMbP5V5FWuDMPiZkrBSqWhDBkFlR0yBuFWBmMq8PORGpXIiw3 w1yKjr6nn/KKqicShQlH3Bd8bJAJ4izoYpKQu+LOFdcc39csEIVdYa6PiUgGrTFw a0odfAJTOkKBTybjnpR/pCW/NGn3b3JO+ftI5ScjyTOXjwsjwUQ= =wihW -----END PGP SIGNATURE----- --vzjspref3jc5jdwj--