Thread: PreparedStatement rounds doubles to scale 14 during update
Hi,
I have the following problem with postgresql-8.1-407.jdbc3.jar used
against a 8.1 version backend.
A table "chemterms_cols_test" has a column "logp" defined as:
logp numeric(30,17)
The double value 6.118992224252588 gets rounded to 6.11899222425259000
during updates with PreparedStatements using place-holder. (There is
no rounding with Statement and the value specified in the SQL
literally.) Is this the expected behaviour?
Below is the code that can be used to test this behaviour ("cd_id" is
the primary key):
public static void main(String[] args) {
try {
Connection conn = DBTools.getConnectionHandler().getConnection();
conn.setAutoCommit(false);
int cdId = 1;
double dblValue = 6.118992224252588;
String sql =
"update chemterms_cols_test set logp = ? where cd_id = ?";
try {
PreparedStatement pstmt = conn.prepareStatement(sql);
try {
pstmt.setDouble(1, dblValue);
pstmt.setInt(2, 1);
pstmt.executeUpdate();
conn.commit();
} finally {
pstmt.close();
}
ResultSet rs = null;
sql = "select logp from chemterms_cols_test where cd_id = ?";
pstmt = conn.prepareStatement(sql);
try {
pstmt.setInt(1, 1);
rs = pstmt.executeQuery();
rs.next();
double actual = rs.getDouble(1);
System.out.println("actual=" + actual + ", expected="
+ dblValue + ", equals? : " + (actual == dblValue));
} finally {
try {
if (rs != null) {
rs.close();
}
} finally {
pstmt.close();
}
}
} finally {
conn.close();
}
} catch (Throwable tbl) {
tbl.printStackTrace();
System.exit(1);
}
}
Thanks
Peter
Peter Kovacs wrote: > Hi, > > I have the following problem with postgresql-8.1-407.jdbc3.jar used > against a 8.1 version backend. > > A table "chemterms_cols_test" has a column "logp" defined as: > > logp numeric(30,17) > > The double value 6.118992224252588 gets rounded to 6.11899222425259000 > during updates with PreparedStatements using place-holder. (There is > no rounding with Statement and the value specified in the SQL > literally.) Is this the expected behaviour? The problem is in the conversion from double to numeric in the server. The server-side float8 data type doesn't have enough precision for that many decimal places. Because converting between numeric and floating-point data types almost always leads to rounding errors at some point, I would strongly recommend sticking to base-10 data types everywhere if you care about the precision. If you use BigDecimal instead of double in your java-program, it works fine. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Heikki, Thank you for your reply. What are base-10 datatypes? Are they numeric types? I cannot find them in the backend documentation. Is there a mapping of Java types to backend types documented somewhere? Thanks a lot Peter On 9/5/07, Heikki Linnakangas <heikki@enterprisedb.com> wrote: > Peter Kovacs wrote: > > Hi, > > > > I have the following problem with postgresql-8.1-407.jdbc3.jar used > > against a 8.1 version backend. > > > > A table "chemterms_cols_test" has a column "logp" defined as: > > > > logp numeric(30,17) > > > > The double value 6.118992224252588 gets rounded to 6.11899222425259000 > > during updates with PreparedStatements using place-holder. (There is > > no rounding with Statement and the value specified in the SQL > > literally.) Is this the expected behaviour? > > The problem is in the conversion from double to numeric in the server. > The server-side float8 data type doesn't have enough precision for that > many decimal places. Because converting between numeric and > floating-point data types almost always leads to rounding errors at some > point, I would strongly recommend sticking to base-10 data types > everywhere if you care about the precision. > > If you use BigDecimal instead of double in your java-program, it works fine. > > -- > Heikki Linnakangas > EnterpriseDB http://www.enterprisedb.com >
Peter Kovacs wrote: > Heikki, > > Thank you for your reply. > > What are base-10 datatypes? Are they numeric types? I cannot find them > in the backend documentation. I meant any data type that can accurately represent a base-10 decimal number. In PostgreSQL, that's numeric. In Java, it's BigDecimal. If you're not familiar with the differences between floating point and decimal numbers, I'd suggest you to read on it. This python tutorial for example has a pretty good chapter on the issues: http://docs.python.org/tut/node16.html > Is there a mapping of Java types to backend types documented somewhere? There is for mapping between Java types and SQL types, see "Getting Started with with the JDBC API" by Sun: http://java.sun.com/j2se/1.5.0/docs/guide/jdbc/getstart/mapping.html -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Thank you for the python reference, it is very instructive indeed. I find it strange that the type mapping specification is in a tutorial by Sun and not in a more formal document. Thanks Peter On 9/5/07, Heikki Linnakangas <heikki@enterprisedb.com> wrote: > Peter Kovacs wrote: > > Heikki, > > > > Thank you for your reply. > > > > What are base-10 datatypes? Are they numeric types? I cannot find them > > in the backend documentation. > > I meant any data type that can accurately represent a base-10 decimal > number. In PostgreSQL, that's numeric. In Java, it's BigDecimal. > > If you're not familiar with the differences between floating point and > decimal numbers, I'd suggest you to read on it. This python tutorial for > example has a pretty good chapter on the issues: > http://docs.python.org/tut/node16.html > > > Is there a mapping of Java types to backend types documented somewhere? > > There is for mapping between Java types and SQL types, see "Getting > Started with with the JDBC API" by Sun: > > http://java.sun.com/j2se/1.5.0/docs/guide/jdbc/getstart/mapping.html > > -- > Heikki Linnakangas > EnterpriseDB http://www.enterprisedb.com >
On 5-Sep-07, at 9:13 AM, Peter Kovacs wrote: > Thank you for the python reference, it is very instructive indeed. > > I find it strange that the type mapping specification is in a tutorial > by Sun and not in a more formal document. > Welcome to SUN API documentation .... Dave > Thanks > Peter > > On 9/5/07, Heikki Linnakangas <heikki@enterprisedb.com> wrote: >> Peter Kovacs wrote: >>> Heikki, >>> >>> Thank you for your reply. >>> >>> What are base-10 datatypes? Are they numeric types? I cannot find >>> them >>> in the backend documentation. >> >> I meant any data type that can accurately represent a base-10 decimal >> number. In PostgreSQL, that's numeric. In Java, it's BigDecimal. >> >> If you're not familiar with the differences between floating point >> and >> decimal numbers, I'd suggest you to read on it. This python >> tutorial for >> example has a pretty good chapter on the issues: >> http://docs.python.org/tut/node16.html >> >>> Is there a mapping of Java types to backend types documented >>> somewhere? >> >> There is for mapping between Java types and SQL types, see "Getting >> Started with with the JDBC API" by Sun: >> >> http://java.sun.com/j2se/1.5.0/docs/guide/jdbc/getstart/mapping.html >> >> -- >> Heikki Linnakangas >> EnterpriseDB http://www.enterprisedb.com >> > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: explain analyze is your friend
Peter Kovacs wrote: > I find it strange that the type mapping specification is in a tutorial > by Sun and not in a more formal document. It's in the JDBC spec too. -O
One last question: How come that Java double, which is an 8 byte size floating point number, cannot fit in PostgreSQL's double precision type, which is equally a floating point type and 8 bytes in size? Thanks Peter On 9/5/07, Heikki Linnakangas <heikki@enterprisedb.com> wrote: > Peter Kovacs wrote: > > Heikki, > > > > Thank you for your reply. > > > > What are base-10 datatypes? Are they numeric types? I cannot find them > > in the backend documentation. > > I meant any data type that can accurately represent a base-10 decimal > number. In PostgreSQL, that's numeric. In Java, it's BigDecimal. > > If you're not familiar with the differences between floating point and > decimal numbers, I'd suggest you to read on it. This python tutorial for > example has a pretty good chapter on the issues: > http://docs.python.org/tut/node16.html > > > Is there a mapping of Java types to backend types documented somewhere? > > There is for mapping between Java types and SQL types, see "Getting > Started with with the JDBC API" by Sun: > > http://java.sun.com/j2se/1.5.0/docs/guide/jdbc/getstart/mapping.html > > -- > Heikki Linnakangas > EnterpriseDB http://www.enterprisedb.com >
--- Dave Cramer <pg@fastcrypt.com> wrote: > > On 5-Sep-07, at 9:13 AM, Peter Kovacs wrote: > > > Thank you for the python reference, it is very instructive indeed. > > > > I find it strange that the type mapping specification is in a tutorial > > by Sun and not in a more formal document. > > > Welcome to SUN API documentation .... I found this link, maybe it is a little better: http://java.sun.com/javase/6/docs/technotes/guides/jdbc/getstart/mapping.html#1004845 Regards, Richard Broersma Jr.
Peter Kovacs wrote:
> One last question:
>
> How come that Java double, which is an 8 byte size floating point
> number, cannot fit in PostgreSQL's double precision type, which is
> equally a floating point type and 8 bytes in size?
Hmm. I'm not sure when the truncation happens. The value might actually
still be intact when it's converted from text representation to double,
and loses precision in the conversion from float8 to decimal. It's
possible to have a floating point value in the database that you can't
get out in text format without losing precision. Consider this for example:
postgres=# SELECT
'6.118992224252588'::float8,
'6.11899222425259'::float8,
'6.11899222425259'::float8 = '6.118992224252588'::float8;
float8 | float8 | ?column?
------------------+------------------+----------
6.11899222425259 | 6.11899222425259 | f
(1 row)
Both values are rounded to the same value on output, but if you compare
them, they're actually not equal.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
On Wed, 5 Sep 2007, Heikki Linnakangas wrote: > Peter Kovacs wrote: >> One last question: >> >> How come that Java double, which is an 8 byte size floating point >> number, cannot fit in PostgreSQL's double precision type, which is >> equally a floating point type and 8 bytes in size? > > Hmm. I'm not sure when the truncation happens. The value might actually > still be intact when it's converted from text representation to double, > and loses precision in the conversion from float8 to decimal. It's > possible to have a floating point value in the database that you can't > get out in text format without losing precision. Consider this for example: > > postgres=# SELECT > '6.118992224252588'::float8, > '6.11899222425259'::float8, > '6.11899222425259'::float8 = '6.118992224252588'::float8; > float8 | float8 | ?column? > ------------------+------------------+---------- > 6.11899222425259 | 6.11899222425259 | f > (1 row) > > Both values are rounded to the same value on output, but if you compare > them, they're actually not equal. > Actually you'll see different behavior with "SET extra_float_digits=2". Is there a reason we shouldn't set this when we create a connection? Kris Jurka
Kris Jurka <books@ejurka.com> writes:
> Actually you'll see different behavior with "SET extra_float_digits=2".
> Is there a reason we shouldn't set this when we create a connection?
Makes sense to me.
regards, tom lane
On Sun, 7 Oct 2007, Tom Lane wrote: > Kris Jurka <books@ejurka.com> writes: >> Actually you'll see different behavior with "SET extra_float_digits=2". >> Is there a reason we shouldn't set this when we create a connection? > > Makes sense to me. > OK, done for 8.3dev. Kris Jurka