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+
Tom>Hmm, but it's done that for a long while, no?'

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