Jasper Report: unable to get value for field 'x' of class 'org.postgresql.util.PGmoney'

Question

When I need to retrieve some information in Order table, jasper cannot cast from PGmoney to double. I searched google first, but no have result of this.

You know how to fix it?

Note: I use PostgreSQL database.

Answer 1

This is one of the several reasons the PostgreSQL money type was deprecated and should be avoided. Oddly newer versions of the same documentation don't show the deprecation warning but I and others disagree with that and think its use should be discouraged.

If at all possible, change your schema to use numeric instead, like numeric(17,2) if you only want to store whole-number cents, or something more precise for intermediate values. You'll have a nightmare of a time working with money in HQL, to the point where even Java's BigDecimal class (usually used to map numeric fields) is better despite the awfully clumsy syntax of itsw arithmetic.

I'd do an ALTER TABLE blah ALTER COLUMN blahcol TYPE numeric(17,2) USING ( regexp_replace(blahcol::text, '[$,]', '', 'g')::numeric ); and forget the money type existed if I were you.