PreparedStatement, float values and postgresql

user_5649810 0
Viewed 1140

Why PreparedStatement with setFloat gives a different result (with less precision) from direct INSERT ?

The code below shows the issue:

    Connection conn = createConnection();

/* here works as expected */
    String sql = "INSERT INTO teste values (1, -50.505050)";
    PreparedStatement ps = conn.prepareStatement(sql);
    ps.executeUpdate();

/* below, all values were saved with less precision */
    sql = "INSERT INTO teste values (?, ?)";
    ps = conn.prepareStatement(sql);

    ps.setInt(1, 2);
    ps.setFloat(2, -50.505050f);
    ps.executeUpdate();

    ps.setInt(1, 3);
    ps.setFloat(2, (float) -50.505050);
    ps.executeUpdate();

    ps.setInt(1, 4);
    ps.setFloat(2, Float.valueOf(-50.505050f));
    ps.executeUpdate();

    ps.setInt(1, 5);
    ps.setFloat(2, Float.valueOf("-50.505050"));
    ps.executeUpdate();

// with double works ok, but how? //
    ps.setInt(1, 6);
    ps.setDouble(2, -50.505050);
    ps.executeUpdate();

    ps.close();
    conn.close();

The database is PostgreSQL, with two columns, an id (bigint) and valor (numeric (9,6)).

Here is the output, from pgadmin4.

pgadmin4 and saved data

The only relevant dependency in pom.xml is below:

    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <version>42.2.8</version>
    </dependency>

The script I used for CREATE the table.

CREATE TABLE public.teste
(
    id bigint NOT NULL,
    valor numeric(9,6),
    CONSTRAINT teste_pkey PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.teste
    OWNER to postgres;

I am using Java 11.

prepared-statement jdbc postgresql database java
1 Answers

A float has from 6 to 9 significant decimal digits precision.
A double has from 15 to 17 significant decimal digits precision.
-50.505050 is 8 digits, which skirts the limit of what a float can handle.

You can see this for yourself if you print it.

System.out.printf("%.6f%n", -50.505050f);
System.out.println(new BigDecimal(-50.505050f));
System.out.println(new BigDecimal(-50.505050d));

Output

-50.505051
-50.5050506591796875
-50.5050499999999971123543218709528446197509765625

The database type numeric(9,6) should be stored in Java as a BigDecimal. Alternatively, a double can be used, but float doesn't have the significant decimal digits precision needed to store such values.

Unless you are severely constrained by Java memory (extremely rare), never use float. With only 6 digits of precision guaranteed, it is simply not useful/reliable, so don't use it.

user_52211490
Related