Driver build: 9.0-801 Server version: 9.0 (Windows x64)
From a Java/JDBC perspective, the handling of the money data type seems broken. My code is DBMS independent and reasonably generic, which means that the code makes extensive use of prepared statements and the getObject()/setObject() method calls. I am able to insert money data by writing a custom class that extends PGmoney, allowing me to control the string format through PGmoney.getValue(). However, data successfully inserted cannot be retrieved via getObject():
org.postgresql.util.PSQLException: Bad value for type double : 12,345,789,000,000.00 at org.postgresql.jdbc2.AbstractJdbc2ResultSet.toDouble(AbstractJdbc2ResultSet.java:2863) at org.postgresql.jdbc2.AbstractJdbc2ResultSet.toDouble(AbstractJdbc2ResultSet.java:2863) at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getDouble(AbstractJdbc2ResultSet.java:2220) at org.postgresql.jdbc2.AbstractJdbc2ResultSet.internalGetObject(AbstractJdbc2ResultSet.java:136) at org.postgresql.jdbc3.AbstractJdbc3ResultSet.internalGetObject(AbstractJdbc3ResultSet.java:38) at org.postgresql.jdbc4.AbstractJdbc4ResultSet.internalGetObject(AbstractJdbc4ResultSet.java:298) at org.postgresql.jdbc2.AbstractJdbc2ResultSet.getObject(AbstractJdbc2ResultSet.java:2541) at com.cst.chetx.data.map.postgres.PostgresDataMapTest.test_2(PostgresDataMapTest.java:323)
IMO the root of the problem involves the typing of money from a JDBC perspective. The table metadata reports the type of a money attribute as standard type java.sql.Types.DOUBLE, dbms-specific type money. My first issue is that the prepared statement will not accept a Double value via setObject() for a money attribute, even though the metadata says that it is a Double - the only thing I could get to work was a PGmoney extension. Second, once a value is inserted into a money attribute, a result set insists on converting it into a Double, with results like the above.
My suggestions to fix these issues are the following:
1. The type of a money field could be java.sql.Types.OTHER with dbms-specific type money. This would give fair warning that there's something, er, interesting about money-type attributes. Note that mapping money to OTHER would not necessarily stop a result set from implementing the getDouble() method for money types - code using this method could still work without change. In this case ResultSet.getObject() should IMO return a PGmoney instance.
2. If OTHER isn't acceptible for whatever reason, then use java.sql.Types.DECIMAL or NUMERIC, again with dbms-specific type money. In this case PreparedStatement.setObject() should always accept a BigDecimal (in addition to a PGmoney), as long as the precision and scale were acceptible. As in the first case, ResultSet.getObject() should return a PGmoney instance.