1

I am working with the Postgres JDBC driver (postgresql-42.2.5.jar) and PostgreSQL 10.6 RDBMS.

I have a result set, and 3 of its columns are of type money (on the DB level). And I have a result set meta data object.

When the column type is money, the driver's method getColumnType() returns 8 for the money columns.

I checked, value of 8 means Double.

But the value returned from the DB is something like $1,312,536.00 or say 1,312,536.00.

So when I try to call rs.getObject() on that column index, it errors out with this:

    org.postgresql.util.PSQLException: Bad value for type double: 1,312,536.00
        at org.postgresql.jdbc.PgResultSet.toDouble(PgResultSet.java:2934)
        at org.postgresql.jdbc.PgResultSet.getDouble(PgResultSet.java:2323)
        at org.postgresql.jdbc.PgResultSet.internalGetObject(PgResultSet.java:194)
        at org.postgresql.jdbc.PgResultSet.getObject(PgResultSet.java:2572)
        at org.apache.commons.dbcp2.DelegatingResultSet.getObject(DelegatingResultSet.java:733)
        at org.apache.commons.dbcp2.DelegatingResultSet.getObject(DelegatingResultSet.java:733)

So it seems the driver cannot talk to its own DB when it comes to money type.

This seems like a bug to me.

Seems that PG-JDBC, they had a bug open for this

https://github.com/pgjdbc/pgjdbc/issues/100

but they closed it due to lack of response instead of fixing it.

Any ideas how to solve this?

Is it really a bug or am I missing something?

peter.petrov
  • 38,363
  • 16
  • 94
  • 159
  • try to cast `money` to `numeric` and see if that works (e.g `select cast(price as numeric) from table` – dsp_user Feb 28 '19 at 11:58
  • 1
    @dsp_user I want to use money. Not to avoid the problem. I know how to avoid it, I can e.g. just use varchar in Postgres, and String in Java. – peter.petrov Feb 28 '19 at 12:01
  • 3
    That is a shortcoming of the money data type - the simple solution is to not use that data type at all. I have never understand what the benefit of it (at some point the Postgres team even thought about deprecating it) - you can't even store multiple currencies with it. Especially because the currency is determined by the session, so for one user it might be `$` and for another user the same value might be displayed as `€` - who would want that? –  Feb 28 '19 at 12:05
  • @a_horse_with_no_name But e.g. in SQL Server money is just money, the money value. When I query a money column, it does not return to me the $ of £. Why would Postgres associate also a currently symbol with it? Why would it return this symbol in result sets? I don't think this is a shortcoming of the money type per se. It's a shortcoming of how certain things around it are implemented, it seems. – peter.petrov Feb 28 '19 at 12:22
  • Well, Postgres is not SQL Server. That's the way the money type was designed in Postgres (for whatever reasons). There is no such thing as "the money type per se" as that is not part of the SQL standard. You need to either cast the column in your query if you want to stick with the dreaded money type (e.g. `price::decimal`) or simply use standard type such as `decimal` directly. But Java can't convert a string like `$1,312,536.00` to a valid number –  Feb 28 '19 at 12:29
  • @a_horse_with_no_name OK, yeah I assumed `money` is part of the standard. OK then, I see. So it seems the state of the money thing is not perfect but I have to accept it :) Thanks for clarifying. – peter.petrov Feb 28 '19 at 12:52
  • I can only repeat my recommendation: do not use the `money` type. If you have to chance to undo that decision, do it. –  Feb 28 '19 at 13:07
  • @a_horse_with_no_name Yep, got it. Thanks again. – peter.petrov Feb 28 '19 at 14:25
  • I just came across this error - I find it weird that the jdbc driver cannot read the field out of the box. I used getObject to read it. It doesn't matter it the money type is useful or not, if it is there it should be supported. If the money type is not useful, then get rid of id. It's a waste of time. I wasted time testing this type, writing this message, creating the field in the first place and so on. – boggy Oct 02 '20 at 06:05
  • @costa I had the same frustration when I posted this question. Unfortunately, that's what we get with free software sometimes. The driver cannot talk to its own DB when it comes to the money type. That is so weird. I would be able to understand if the vendors of the driver and the DB were different... but in this case, I don't get it. – peter.petrov Oct 02 '20 at 14:16
  • I am going to look into it to see if it's easy to fix. – boggy Oct 04 '20 at 06:40
  • https://github.com/pgjdbc/pgjdbc/issues/1835: no plan to fix this. don't use money. I guess that's that. – boggy Oct 05 '20 at 19:38
  • @costa Yeah, see also the bug I referenced in my post: https://github.com/pgjdbc/pgjdbc/issues/100 It was also closed without any fix, it seems. – peter.petrov Oct 06 '20 at 10:00

0 Answers0