pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedHow can I accurately infer the output of a SELECT statement FROM a table with floating-point data type?
5+ messages / 5 participants
[nested] [flat]
* How can I accurately infer the output of a SELECT statement FROM a table with floating-point data type?
@ 2023-10-19 03:45 毛毛 <krave@163.com>
0 siblings, 3 replies; 5+ messages in thread
From: 毛毛 @ 2023-10-19 03:45 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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!
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How can I accurately infer the output of a SELECT statement FROM a table with floating-point data type?
@ 2023-10-19 04:12 Tom Lane <tgl@sss.pgh.pa.us>
parent: 毛毛 <krave@163.com>
2 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2023-10-19 04:12 UTC (permalink / raw)
To: 毛毛 <krave@163.com>; +Cc: pgsql-general@lists.postgresql.org
=?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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How can I accurately infer the output of a SELECT statement FROM a table with floating-point data type?
@ 2023-10-19 04:14 David G. Johnston <david.g.johnston@gmail.com>
parent: 毛毛 <krave@163.com>
2 siblings, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2023-10-19 04:14 UTC (permalink / raw)
To: 毛毛 <krave@163.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How can I accurately infer the output of a SELECT statement FROM a table with floating-point data type?
@ 2023-10-19 09:47 Tomek <tomekphotos@gmail.com>
parent: 毛毛 <krave@163.com>
2 siblings, 0 replies; 5+ messages in thread
From: Tomek @ 2023-10-19 09:47 UTC (permalink / raw)
To: 毛毛 <krave@163.com>; +Cc: pgsql-general@lists.postgresql.org
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):
> 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!
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How can I accurately infer the output of a SELECT statement FROM a table with floating-point data type?
@ 2023-10-21 20:21 Peter J. Holzer <hjp-pgsql@hjp.at>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Peter J. Holzer @ 2023-10-21 20:21 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
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!"
Attachments:
signature.asc (application/pgp-signature, 832B) download
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2023-10-21 20:21 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-19 03:45 How can I accurately infer the output of a SELECT statement FROM a table with floating-point data type? 毛毛 <krave@163.com>
2023-10-19 04:12 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-10-21 20:21 ` Peter J. Holzer <hjp-pgsql@hjp.at>
2023-10-19 04:14 ` David G. Johnston <david.g.johnston@gmail.com>
2023-10-19 09:47 ` Tomek <tomekphotos@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox