pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
How 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>
  2023-10-19 04:12 ` Re: How can I accurately infer the output of a SELECT statement FROM a table with floating-point data type? Tom Lane <tgl@sss.pgh.pa.us>
  2023-10-19 04:14 ` Re: How can I accurately infer the output of a SELECT statement FROM a table with floating-point data type? David G. Johnston <david.g.johnston@gmail.com>
  2023-10-19 09:47 ` Re: How can I accurately infer the output of a SELECT statement FROM a table with floating-point data type? Tomek <tomekphotos@gmail.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 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   ` Re: How can I accurately infer the output of a SELECT statement FROM a table with floating-point data type? Peter J. Holzer <hjp-pgsql@hjp.at>
  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 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 ` Re: How can I accurately infer the output of a SELECT statement FROM a table with floating-point data type? Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-10-21 20:21   ` Peter J. Holzer <hjp-pgsql@hjp.at>
  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

* Re: How can I accurately infer the output of a SELECT statement FROM a table with floating-point data type?
  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:14 ` David G. Johnston <david.g.johnston@gmail.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 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 09:47 ` Tomek <tomekphotos@gmail.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


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