On Fri, 9 Jan 2004, Jonathan Purvis wrote:
> The JDBC driver that ships with PostgreSQL 7.4.1 doesn't insert floats
> of value NaN. As it uses Float.toString(x) to convert the value for
> insertion into the database, it tries to insert NaN instead of 'NaN' and
> gets the error "Attribute 'nan' not found". The same bug occurs for
> doubles and will probably occur for infinite values as well (i haven't
> tested it). This bug also exists in 7.2.4 and in the 7.2.1 version
> currently in Debian stable.
As you noted this is a problem with positive and negative infinity, but
there are some more problems as evidenced by the attached test case.
Double.MIN_VALUE will cause underflow.
Double.MAX_VALUE goes in alright, but comes out as POSITIVE_INFINITY
when trying Float.MAX_VALUE and MIN_VALUE on a real or float4 field they
overflow and underflow respectively.
Kris Jurka
import java.sql.*;
public class Range {
public static void main(String args[]) throws Exception {
Class.forName("org.postgresql.Driver");
Connection conn =
DriverManager.getConnection("jdbc:postgresql://localhost:5750/jurka","jurka","");
setupDB(conn);
insert(conn,Double.NaN,0);
insert(conn,Double.MAX_VALUE,0);
insert(conn,Double.MIN_VALUE,0);
insert(conn,Double.NEGATIVE_INFINITY,0);
insert(conn,Double.POSITIVE_INFINITY,0);
insert(conn,0,Float.MIN_VALUE);
insert(conn,0,Float.MAX_VALUE);
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT a,b FROM floattable");
while (rs.next()) {
System.out.println(rs.getDouble(1)+", "+rs.getFloat(2));
}
rs.close();
stmt.close();
conn.close();
}
private static void setupDB(Connection conn) throws SQLException {
Statement stmt = conn.createStatement();
try {
stmt.executeUpdate("DROP TABLE floattable");
} catch (SQLException sqle) {
conn.rollback();
}
stmt.executeUpdate("CREATE TABLE floattable(a float, b real)");
stmt.executeUpdate("INSERT INTO floattable VALUES ('nan','nan')");
}
private static void insert(Connection conn, double d, float f) {
try {
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO
floattable(a,b) VALUES (?,?)");
pstmt.setDouble(1,d);
pstmt.setFloat(2,f);
pstmt.executeUpdate();
pstmt.close();
} catch (SQLException sqle) {
sqle.printStackTrace();
try {
conn.rollback();
} catch (SQLException e) { }
}
}
}
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
