以十进制形式选择浮点数 [英] Selecting floating point numbers in decimal form

问题描述

我在PostgreSQL表中有一个小数字：

  test =＃CREATE TABLE test（r real）; 
 CREATE TABLE 
 test =＃INSERT INTO test VALUES（0.00000000000000000000000000000000000000000009）; 
 INSERT 0 1 
  

当我运行以下查询时，它返回的数字为 8.96831e-44 ：

  test =＃SELECT * FROM test; 
r 
 ------------- 
 8.96831e-44 
（1 row）
  

如何以小数形式（ 0.00000000000000000000000000000000000000000009 <= code> / code>）而不是科学记数法？我也很高兴与 0.0000000000000000000000000000000000000000000896831 太。不幸的是，我不能改变表格，我并不在意精度的损失。

（我玩过 to_char 暂时没有成功。）

解决方案

您的值，

  0.00000000000000000000000000000000000000000009 
  

不能用32位IEEE754浮点数精确表示。您可以在此计算器中检查的确切值

根据计算器，您可以尝试使用 double precision （64位）来存储它，这似乎是一个确切的表示形式。 Patricia表明，这只是计算器四舍五入的价值，即使明确地要求不要... Double意味着更精确一点，但仍然没有确切的价值，作为这个数字不能用有限数量的二进制数字表示。 （谢谢，帕特里夏，一个教训（再次）：不要相信你在Intertubez上看到什么）

$ b 正常情况下，你应该使用NUMERIC（精度，比例尺）的格式，这将存储的数字精确地取回正确的价值。
$ b

然而，您的存储价值似乎有一个规模大于postgres允许（这似乎是30）精确的十进制表示法。如果你不想做计算，只是把它们存储起来（这不是一个很常见的情况，我承认），你可以尝试将它们存储为字符串...（但这是丑陋的...）

编辑

这个to_char问题似乎是一个 已知错误 ...

...

Quote：

我对此的直接反应是float8值没有57位
的精度。如果你期待格式字符串做
有用，你应该把它应用到一个数字列而不是一个双
精确的一个。

可能我们可以用这些东西来使这个特定的案例像你所期望的那样工作
，但总是会有类似的
案例无法工作，因为精确度不在那里。

快速查看代码，得到0的原因。是
在15位数之后舍去，以确保它不会打印垃圾。也许
在
小于1的情况下可能会更聪明一些，但这不是一个简单的改变。

（从这里 a>）

然而，我觉得这是不可辩护的。恕我直言，一个双精度（IEEE754 64位浮点准确）将始终有〜15个重要的十进制数字，如果该值符合类型...

推荐阅读：

• 每个计算机科学家应该知道的关于浮点运算的知识 postgresql.org/docs/8.3/static/datatype-numeric.htmlrel =nofollow> Postgres数字类型
BUG＃6217：TO_CHAR（）给出了不正确的输出非常小的浮点值

I've a small number in a PostgreSQL table:

test=# CREATE TABLE test (r real);
CREATE TABLE
test=# INSERT INTO test VALUES (0.00000000000000000000000000000000000000000009);
INSERT 0 1

When I run the following query it returns the number as 8.96831e-44:

test=# SELECT * FROM test;
      r      
-------------
 8.96831e-44
(1 row)

How can I show the value in psql in its decimal form (0.00000000000000000000000000000000000000000009) instead of the scientific notation? I'd be happy with 0.0000000000000000000000000000000000000000000896831 too. Unfortunately I can't change the table and I don't really care about loss of precision.

(I've played with to_char for a while with no success.)

解决方案

Real in Postgres is a floating point datatype, stored on 4 bytes, that is 32 bits.

Your value,

0.00000000000000000000000000000000000000000009

Can not be precisely represented in a 32bit IEEE754 floating point number. You can check the exact values in this calculator

You cold try and use double precision (64bits) to store it, according to the calculator, that seems to be an exact representation. NOT TRUE Patricia showed that it was just the calculator rounding the value, even though explicitly asking it not to... Double would mean a bit more precision, but still no exact value, as this number is not representable using finite number of binary digits. (Thanks, Patricia, a lesson learnt (again): don't believe what you see on the Intertubez)

Under normal circumstances, you should use a NUMERIC(precision, scale) format, that would store the number precisely to get back the correct value.

However, your value to store seems to have a scale larger than postgres allows (which seems to be 30) for exact decimal represenations. If you don't want to do calculations, just store them (which would not be a very common situation, I admit), you could try storing them as strings... (but this is ugly...)

EDIT

This to_char problem seems to be a known bug...

Quote:

My immediate reaction to that is that float8 values don't have 57 digits of precision. If you are expecting that format string to do something useful you should be applying it to a numeric column not a double precision one.

It's possible that we can kluge things to make this particular case work like you are expecting, but there are always going to be similar-looking cases that can't work because the precision just isn't there.

In a quick look at the code, the reason you just get "0." is that it's rounding off after 15 digits to ensure it doesn't print garbage. Maybe it could be a bit smarter for cases where the value is very much smaller than 1, but it wouldn't be a simple change.

(from here)

However, I find this not defendable. IMHO a double (IEEE754 64bit floating point to be exact) will always have ~15 significant decimal digits, if the value fits into the type...

Recommended reading:

• What Every Computer Scientist Should Know About Floating-Point Arithmetic
• Postgres numeric types
• BUG #6217: to_char() gives incorrect output for very small float values

这篇关于以十进制形式选择浮点数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！