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 1qtKQA-00By5I-QU for pgsql-general@arkaria.postgresql.org; Thu, 19 Oct 2023 04:14:06 +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 1qtKPA-00CbaL-K5 for pgsql-general@arkaria.postgresql.org; Thu, 19 Oct 2023 04:13:05 +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 1qtKPA-00Cba5-4v for pgsql-general@lists.postgresql.org; Thu, 19 Oct 2023 04:13:04 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qtKP4-001CAB-2d for pgsql-general@lists.postgresql.org; Thu, 19 Oct 2023 04:13:03 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 39J4Cp7E3259197; Thu, 19 Oct 2023 00:12:51 -0400 From: Tom Lane To: =?GBK?B?w6vDqw==?= cc: 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? In-reply-to: <10b02a08.28b4.18b460a2f09.Coremail.krave@163.com> References: <10b02a08.28b4.18b460a2f09.Coremail.krave@163.com> Comments: In-reply-to =?GBK?B?w6vDqw==?= message dated "Thu, 19 Oct 2023 11:45:54 +0800" MIME-Version: 1.0 Content-Type: text/plain; charset="UTF-8" Content-ID: <3259195.1697688771.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Thu, 19 Oct 2023 00:12:51 -0400 Message-ID: <3259196.1697688771@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk =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? 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. Keep in mind that the nominal precision, such as 6 digits for float4, is a *lower bound* on the number of decimal digits that will be stored accurately, whereas supporting exact round-trip I/O requires an *upper bound* number of digits. In short, no, there is not a trivial way to predict the number of digits emitted. If you have a problem with that, maybe you should be using type numeric instead. You can find more info by looking around for info about the Ry=C5=AB float output algorithm, eg here: https://github.com/ulfjack/ryu regards, tom lane