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….
https://stackoverflow.com/questions/62600304/postgressql-driver-version-12-rounding-java-float-value-to-six-digits
Thanks
Param