3

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.

  • As you can read in [Monetary Types](http://www.postgresql.org/docs/9.0/interactive/datatype-money.html) post the `regexp_replace` function can help you – Alex K Sep 04 '12 at 11:14
  • Hi Alex K, I have a problem, I use hql language to query data, I read the page you give me, but it told me to cast money to numeric and then double. But in hql, I cannot cast to numeric or double(absolutely). When I cast it to string(error). –  Sep 04 '12 at 12:26

1 Answers1

6

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • The discussion you link to took place in 2008. Several improvements came with version 9.1 - addressing those points. `Add support for dividing money by money (Andy Balholm)`, `Add support for casting between money and numeric (Andy Balholm)` and `Add support for casting from int4 and int8 to money (Joey Adams)`. What you cite from the 8.2 manual was before this was changed with 8.3: `Widen the MONEY data type to 64 bits (D'Arcy Cain) This greatly increases the range of supported MONEY values.` So, while I would use `numeric`, too, I don't see why `money` should be avoided on principle. – Erwin Brandstetter Sep 04 '12 at 13:54
  • @ErwinBrandstetter Ah, I'd accidentally gone back to the old version of the manual when looking at the cast issues etc. It does look much better now. I still don't see what *advantage* `money` brings, but it's better for sure. Its format will still likely be painful when working with query generators etc, though, as they're often all about lowest-common-denominator features. – Craig Ringer Sep 04 '12 at 14:02
  • I guess you are right there. As I said, I would use `numeric`, too. Just to put things into perspective ... – Erwin Brandstetter Sep 04 '12 at 14:09
  • So, the main point we shouldn't use money type, right? So I changed to others, and it worked. My problem was when I'm in postgres, I could change from money to numeric through text. But in hibernate, it could't understand that. –  Sep 05 '12 at 06:49
  • @justin Yep, that's classic for ORMs. They're often very strict about formats, data types, etc, but expect the database to be quite loose about what it expects. Both these things can be challenges, and it's often worth sticking to fairly basic database features when working via an ORM, using native queries and stored procs for more interesting stuff. – Craig Ringer Sep 05 '12 at 08:51