Re: PostgresSQL 10 | Driver 42.2.5 | Float Conversion Issue

TL;DR: pgjdbc 9.2-1001+ sends floats as binary by default which causes the conversion issue at the database side.

The workaround is to add binaryTransferDisable=700,701 driver connection property (the ids are for FLOAT4 and FLOAT4_ARRAY)

OP upgraded from 9.1-901 to 42.2.5 which explains the difference.

---

Vladimir> initial packet, then it upgrades to extra_float_digist=3 in case server
Vladimir> version is 9.0+

It was released in pgjdbc 9.1-901 on 2011-04-18: https://github.com/pgjdbc/pgjdbc/commit/bcdd8065e4d547a990266ad9d4d90318e90d5a14

And it happens to be exactly the version mentioned in the first mail.

It looks like the interesting change is as follows.

9.1-901 always used bindLiteral(parameterIndex, Float.toString(x), Oid.FLOAT8); if client uses setFloat(...) (which is exactly the call in the initial mail).

In other words, it sent the value as a string, and Java converts 1234567f as 1234567, so the value survived.

Don't ask me why the driver used FLOAT8 for sending float4 data :) I bet it was made to workaround precision loss issues like we see now.

Dave made the change in 2005 https://github.com/pgjdbc/pgjdbc/commit/ccc8f4c37f9ff1807b0a2efb0fb58e8154311a96

9.2-1001 was released on 2012-10-31 and it introduced binary bind for certain primitives (see 

https://github.com/pgjdbc/pgjdbc/commit/270def76a49500aacb72815bc40892b97af1b6d6 ),

so the driver sends float4 as binary, and the database can't convert it to numeric anymore.

Vladimir