为什么postgresql不会存储我的整个（PHP）浮点值？

I try to store the PHP floating point value 63.59072952118762 into a double precision column in postgres. Postgres stores the value as 63.59073. Does anyone know why? 8 byte should be more than enough for that value. I've tried with the data type numeric, which works when specifying the precision, but that shouldn't really be necessary.

我尝试将PHP浮点值63.59072952118762存储到postgres中的双精度列中。 Postgres将该值存储为63.59073。有谁知道为什么?对于该值,8字节应该足够了。我尝试过数据类型numeric,它在指定精度时起作用,但这不应该是必要的。

Update: The same problem is present when trying to store 63.5907295, so the suggestion that something happens with the double before it's getting stored seems realistic.

更新:尝试存储63.5907295时存在同样的问题,因此建议在存储之前发生的事情似乎是现实的。

Update II (partly solved): The line where I assign the double parameter looks like this:

更新II(部分解决):我分配double参数的行如下所示:

$stmt->bindParam(4, $this->latitude);

The thing I didn't know is that PDO defaults its param type to string. I changed it to PDO::PARAM INT in lack of a better alternative (PARAM DOUBLE was not an option), and got 10 digits precision in the double stored in postgres (some progress, at least). I've checked that the numeric type works well, so it seems that numeric is the way to go when using PDO and doubles that has to have a precision of more than 10 decimals.

我不知道的是PDO将其param类型默认为string。我把它更改为PDO :: PARAM INT缺少更好的替代方案(PARAM DOUBLE不是一个选项),并且在postgres中存储的double中得到10位精度(至少有一些进展)。我已经检查过数字类型是否运行良好,所以看起来数字是使用PDO时的方法,双精度必须具有超过10位小数的精度。

Anyways, as someone has mentioned, I don't know if it's a must for me to have this kind of precision, but I think the problem in itself deserved to be investigated.

无论如何,正如有人提到的,我不知道这是否必须具备这种精确度,但我认为这个问题本身值得研究。

3 个解决方案

#1

How do you determine what PostgreSQL is storing?

你如何确定PostgreSQL存储的内容?

How do you send the data to PostgreSQL?

你如何将数据发送到PostgreSQL?

How do you get the data back again?

你如何重新获得数据?

How do you display it?

你是如何展示它的?

What type is the column in the database?

数据库中的列是什么类型的?

There are many, many places on the path between PHP and PostgreSQL where there could be confusion about how to represent the data.

PHP和PostgreSQL之间的路径上有很多很多地方可能会混淆如何表示数据。

It is important to explain how data is inserted into the DBMS. Using a literal value in the INSERT statement leads to a different set of problems from using bound parameters. If you wrote the value out in the SQL:

解释如何将数据插入DBMS非常重要。在INSERT语句中使用文字值会导致使用绑定参数的一组不同问题。如果你在SQL中写出了值:

INSERT INTO SomeTable(SomeColumn) VALUES(63.xxxxxxxxx);

and the data was truncated, you'd have a problem down in PostgreSQL. If you bind the variable, you have to be sure to understand what PHP and the PDO PostgresSQL modules do with the value - is it sent as a double, or as a string, and which code deals with the conversion, and so on.

并且数据被截断,你在PostgreSQL中遇到了问题。如果绑定变量,则必须确保理解PHP和PDO PostgresSQL模块对该值的作用 - 它是作为double或字符串发送的,以及哪些代码处理转换,等等。

You run into analogous issues with Perl + DBI + DBD::YourDBMS (DBD::Pg in your case).

您遇到类似的问题与Perl + DBI + DBD :: YourDBMS(在您的情况下为DBD :: Pg)。

#2

Consider using the DECIMAL/NUMERIC type if you need that much precision

如果您需要那么高的精度,请考虑使用DECIMAL / NUMERIC类型

#3

PostgreSQL accepts float(1) to float(24) as selecting the real type, while float(25) to float(53) select double precision.

PostgreSQL接受float(1)到float(24)选择真实类型,而float(25)到float(53)选择双精度。

On most platforms PG, the real type has a range of at least 1E-37 to 1E+37 with a precision of at least 6 decimal digits. The double precision type typically has a range of around 1E-307 to 1E+308 with a precision of at least 15 digits (REF)

在大多数平台PG上,实际类型的范围至少为1E-37到1E + 37,精度至少为6位十进制数。双精度类型通常具有大约1E-307到1E + 308的范围,精度至少为15位(REF)

Which one do you use?

你用哪一个?

#1

How do you determine what PostgreSQL is storing?