RE: PostgresSQL 10 | Driver 42.2.5 | Float Conversion Issue

Many thanks to all on sharing the thoughts.

However, As an application developer, I like to see all the options that I can evaluate and pick the minimal change that can get things working.

So far, I see following are the options that are suggested.

1. Workaround is to add binaryTransferDisable=700,701 driver connection property (the ids are for FLOAT4 and FLOAT4_ARRAY)
2. Use Java's BigDecimal on the Application side while setting the value through prepared statement.

Any other suggestions which I missed that might work in my scenario?

Thanks
Param

-----Original Message-----
From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Sent: Wednesday, October 21, 2020 4:12 AM
To: Vladimir Sitnikov
Cc: David G. Johnston; Andreas Joseph Krogh; Thangavel, Parameswaran; pgsql-jdbc(at)postgresql(dot)org
Subject: Re: PostgresSQL 10 | Driver 42.2.5 | Float Conversion Issue

[EXTERNAL EMAIL]

Vladimir Sitnikov <sitnikov(dot)vladimir(at)gmail(dot)com> writes:
> Tom>Note the waffling immediately after that, though.
> I assume PostgreSQL switched to Ryu around release 12 or so.

> Should float4_numeric switch to Ryu as well?
> I guess it would be both faster and more precise.

Ryu or not, it'd have the issue of sometimes presenting digits the user wasn't expecting, if the user is under the illusion that float is exact.
I'd rather document "this is always rounded to N digits" than have to explain the cases where Ryu doesn't produce an "exact" result.
You don't have to look far for examples, eg

# select 1234567890::float4;
float4
---------------
1.2345679e+09
(1 row)

# select '0.98765456'::float4;
float4
------------
0.98765457
(1 row)

You have to keep in mind that what Ryu does (or claims to do anyway) is produce the shortest decimal sequence that float4in will map back to the same float4 bit pattern. While this does often produce "unsurprising" results, that is not guaranteed once you're past six digits.

Now, I suppose there is something to be said for ensuring that float::numeric and float::text::numeric produce the same answers, which we'd get if we made float4_numeric and float8_numeric use Ryu instead of the current method. But that's not going to change the fact that the OP is doing it wrong by expecting exact results from what he's doing.

[ digs in archives... ] The original thread about the Ryu patch [1] considered this, and ultimately decided not to on the grounds that float4_numeric is marked immutable and therefore we should not change its behavior. I'm not sure if that argument is really irrefutable, but it's something to think about.

In any case, the JDBC list is not where to be complaining if you want a server behavior change.

regards, tom lane

[1] https://www.postgresql.org/message-id/flat/87r2el1bx6.fsf%40news-spur.riddles.org.uk