How can I accurately infer the output of a SELECT statement FROM a table with floating-point data type?
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.
https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-NUMERIC
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!
=?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-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ū float output algorithm, eg here:
https://github.com/ulfjack/ryu
regards, tom lane
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.
https://www.postgresql.org/docs/current/datatype-numeric.
html#DATATYPE-NUMERIC
You also need to consult:
https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-FLOAT
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.
Hi,
May be you think about this ?
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):
Show quoted text
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.https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-NUMERIC
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!
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-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.
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!"