Обсуждение: PostgresSQL 10 | Driver 42.2.5 | Float Conversion Issue
Hi Team,
I am using postgresql version 9.4.1. We are in the process of upgrading to PostgreSQL 10.
While doing so, we have upgraded the JDBC postgres driver from 9.1-901.jdbc4 to 42.2.5 Post the upgrade we are facing the issue with data integrity where the numeric value is getting rounded off.
Scenario:
I am trying to insert a value (float datatype in Java) and in DB it is represented as Numeric.
When I try to store, "1234567" the value is stored as "1234570".
When I try to store, "123456" then the value is stored as "123456"
I am not able to understand the behavior of #1, where the digit 7 is dropped and 6 is getting rounded off to 7. When I try #1 using older driver (9.1-901.jdbc4) then all is good.
Any thoughts on why this behavior, Is this expected?
Java code snippet:
==============
String inserSqlv2 = "INSERT INTO ABC (ID,IDNAME,NAME,FLOAT_VALUE, LASTMODIFIED) VALUES (?,?,?,?,?);";
Float val = Float.valueOf(1234567f);
preparedStatement = conn.prepareStatement(inserSqlv2);
preparedStatement.setString(1, "d77e7ed20c2b650a148df390a8b3bce9");
preparedStatement.setString(2, "f199af000c2b650a397a8c537ba69d71");
preparedStatement.setString(3, "FLOT_CHECK");
preparedStatement.setFloat(4, val);
preparedStatement.setTimestamp(5, now);
preparedStatement.execute();
Table Definition:
=============
CREATE TABLE rsa_rep.abc
(
id character varying(32) COLLATE pg_catalog."default" NOT NULL,
idname character varying(32) COLLATE pg_catalog."default" NOT NULL,
name character varying(1020) COLLATE pg_catalog."default" NOT NULL,
float_value numeric
)
Logs on postgresql:
================
=== when PostgreSQL driver 9.1-901.jdbc4 is used. ===========
2020-06-27 08:07:58.463 GMT [unknown] dba_user 5ef6fe5d.15c8 10/45 0 LOG: execute <unnamed>: INSERT INTO ABC (ID,IDNAME,NAME,FLOAT_VALUE) VALUES ($1,$2,$3,$4)
2020-06-27 08:07:58.463 GMT [unknown] dba_user 5ef6fe5d.15c8 10/45 0 DETAIL: parameters: $1 = 'd77e7ed20c2b650a148df390a8b3bce9', $2 = 'f199af000c2b650a397a8c537ba69d71', $3 = 'FLOT_CHECK', $4 = '1234567'
=== when PostgreSQL driver 42.2.5 is used ===========
2020-06-27 08:08:47.065 GMT PostgreSQL JDBC Driver dba_user 5ef6fe8d.eac 10/63 0 LOG: execute <unnamed>: INSERT INTO ABC (ID,IDNAME,NAME,FLOAT_VALUE) VALUES ($1,$2,$3,$4)
2020-06-27 08:08:47.065 GMT PostgreSQL JDBC Driver dba_user 5ef6fe8d.eac 10/63 0 DETAIL: parameters: $1 = 'd77e7ed20c2b650a148df390a8b3bce9', $2 = 'f199af000c2b650a397a8c537ba69d71', $3 = 'FLOT_CHECK', $4 = '1.234567e+06'
Maven dependency (for reference):
=============================
<dependency>
<groupId>org.postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>42.2.5</version>
</dependency>
<dependency>
<groupId>postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>9.1-901.jdbc4</version>
</dependency>
I have raised the same issue in below site as well….
Thanks
Param
Any thoughts on why this behavior, Is this expected?
preparedStatement.setFloat(4, val);
float_value numeric
On Friday, October 16, 2020, Thangavel, Parameswaran <Parameswaran.Thangavel@rsa.com> wrote:Any thoughts on why this behavior, Is this expected?
Not immediately, can you demonstrate the problem without using JDBC? The JDBC project has its own issues setup on GitHub.
Hi, On 2020-10-20 09:50:46 -0700, David G. Johnston wrote: > So, confirming the following query result on head. > > select '1234567'::float4::numeric; -- 1234570 The reason this happens is that the float[48]->numeric casts are implemented by converting the float value to string using FLT_DIG/DBL_DIG, and then reading that back as a string. Unfortunately that's pretty bogus - FLT_DIG / DBL_DIG are extremely pessimistic; as it turns out rounding a few digits beyond the decimal point isn't the same as before the decimal point. Unfortunately this can't easily be changed, as any such change would e.g. break indexes that include float->numeric casts. Including across major versions, due to pg_upgrade. For text this is not a problem anymore since v12 (with default settings, i.e. extra_float_digits > 0), because we now use ryu for those conversions. But we can't depend extra_float_digits in the case of float->numeric casts, because it's an immutable function :/ Thanks to Andrew Gierth for some pointers. Some discussion links: https://www.postgresql.org/message-id/874lagotif.fsf%40news-spur.riddles.org.uk https://postgr.es/m/26443.1571712071%40sss.pgh.pa.us https://www.postgresql.org/message-id/87y37qmwee.fsf%40news-spur.riddles.org.uk Greetings, Andres Freund