Thread: 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….

https://stackoverflow.com/questions/62600304/postgressql-driver-version-12-rounding-java-float-value-to-six-digits

Thanks

Param