Thread: How can I accurately infer the output of a SELECT statement FROM a table with floating-point data type?
How can I accurately infer the output of a SELECT statement FROM a table with floating-point data type?
From
毛毛
Date:
Hi,
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?
I am working with PostgreSQL 16 and pgAdmin 7.6.
Here is my SQL script:
```
CREATE TABLE TEST (REAL_COLUMN real);
INSERT INTO TEST
VALUES (12345.123456789);
SELECT * FROM TEST;
INSERT INTO TEST
VALUES (12345.123456789);
SELECT * FROM TEST;
```
I consulted the following doc and found that the range of real type is 6 decimal digits precision.
So I thought the output of the SELECT statement should be like: 12345.1 with 6 digits in total.
But it turns out to be 12345.123 with 8 digits in total.
May I know why would this happen?
Do we have a rule I can use to infer the correct output of a floating-point number without running the script?
Thank you for your time and have a great day!
Re: How can I accurately infer the output of a SELECT statement FROM a table with floating-point data type?
From
Tom Lane
Date:
=?GBK?B?w6vDqw==?= <krave@163.com> writes: > Do we have a rule by follow which one can accurately info the output of a SELECT statment FROM a table with floating-pointdata 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ū float output algorithm, eg here: https://github.com/ulfjack/ryu regards, tom lane
Re: How can I accurately infer the output of a SELECT statement FROM a table with floating-point data type?
From
"David G. Johnston"
Date:
On Wednesday, October 18, 2023, 毛毛 <krave@163.com> wrote:
I consulted the following doc and found that the range of real type is 6 decimal digits precision.
You also need to consult:
But in short, no, the result is input specific and you the user are not expected to know or care about such details. The docs say you will get between 6 and 9 digits depending on the value, under default settings. The table gives the minimum as clarified in the prose. There is room in the table to include more detail and it probably should.
David J.
Re: How can I accurately infer the output of a SELECT statement FROM a table with floating-point data type?
From
Tomek
Date:
Hi,
May be you think about this ?
SELECT REAL_COLUMN, to_char(REAL_COLUMN,'9.99999EEEE') 6_significant_numbers_format FROM TEST;
SELECT REAL_COLUMN, to_char(REAL_COLUMN,'9.99999EEEE') 6_significant_numbers_format FROM TEST;
Regards Tomek
czw., 19 paź 2023 o 05:47 毛毛 <krave@163.com> napisał(a):
Hi,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?I am working with PostgreSQL 16 and pgAdmin 7.6.Here is my SQL script:```CREATE TABLE TEST (REAL_COLUMN real);
INSERT INTO TEST
VALUES (12345.123456789);
SELECT * FROM TEST;```I consulted the following doc and found that the range of real type is 6 decimal digits precision.So I thought the output of the SELECT statement should be like: 12345.1 with 6 digits in total.But it turns out to be 12345.123 with 8 digits in total.May I know why would this happen?Do we have a rule I can use to infer the correct output of a floating-point number without running the script?Thank you for your time and have a great day!
Re: How can I accurately infer the output of a SELECT statement FROM a table with floating-point data type?
From
"Peter J. Holzer"
Date:
On 2023-10-19 00:12:51 -0400, Tom Lane wrote:
> =?GBK?B?w6vDqw==?= <krave@163.com> writes:
> > Do we have a rule by follow which one can accurately info the output of a SELECT statment FROM a table with
floating-pointdata 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.
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 = 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 = 12345.1201171875.
That's different, so 7 digits are not enough in this case.
hp
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"