Why won't postgresql store my entire (PHP) float value?

Question

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.

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.

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

$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.

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.

Answer 1

• How do you determine what PostgreSQL is storing?
• How do you send the data to 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.

---

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:

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.

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

Answer 2

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

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)

Which one do you use?

Answer 3

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