PreparedStatement, float values and postgresql

Question

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.

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.

Why are you trying to use a floating point datatype to insert a value to an exact numeric column? Using BigDecimal would be more appropriate, and if you really need to use floating point types, use double, not float. — Mark Rotteveel
– Mark Rotteveel, Commented Jun 5, 2020 at 8:05
I didn't know about the small precision of float types (java). BigDecimal is new to me too. Thanks for the tips !! — lubrum
– lubrum, Commented Jun 5, 2020 at 12:35

Andreas · Accepted Answer · 2020-06-05 00:50:29Z

5

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.

answered Jun 5, 2020 at 0:50

Andreas

160k13 gold badges164 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

lubrum Over a year ago

Thanks. I will use double from now. I did not understand something: if float can handle 6 digits of precision, why -50.505050 didn't work out?

Andreas Over a year ago

@LucianoBrum Because it is an 8-digit number. Note, that the 6-9 significant decimal digits precision is the total number of digits, not just the fractional digits to the right of the decimal point.

Collectives™ on Stack Overflow

PreparedStatement, float values and postgresql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related