11

For a project, I created a table with a column price MONEY NOT NULL column. And I thought it would handle decimals properly, unlike a floating number (i.e. IEEE rounding issues), but I end up having values like $9.94 (string) being returned from the database, instead of 9.94 (numeric). Having to manually manipulate the field value by removing the dollar sign is silly; is there a way to have a MONEY column without a currency sign?

If not, what's the best data type replacement to handle currency values?

Improve this question
5
  • 1
    I'd rather use an integer with an implied number of decimals. – Philᵀᴹ Jan 15 '15 at 18:42
  • 2
    The MONEY datatype in Postgres is for dollars only. ;) – ypercubeᵀᴹ Jan 15 '15 at 18:55
  • @ypercube, So.... other currencies have no rounding issues? Besides, it seems it can be changed through the LC_MONETARY db variable. – Yanick Rochon Jan 15 '15 at 19:02
  • I find money a rather useless datatype. numeric is much better and more flexible. – a_horse_with_no_name Jan 15 '15 at 19:54
  • 1
    MONEY is a great datatype, in theory. It's stored as a 64bit binary integer with an implied, fixed number of decimals. This means that storage is conserved and operations are always as fast and exact as possible (to the specified decimal precision.) It's a pity you can't specify the precision on a column-by-column basis (such as MONEY(2) and MONEY(4)) and that by default it prints numbers in a silly way: $1,200.00 – Tobia Nov 30 '16 at 10:29
11

What about this?

A money value can be cast to numeric without loss of precision. Conversion to other types could potentially lose precision, and must also be done in two stages:

SELECT '52093.89'::money::numeric::float8;

Source: http://www.postgresql.org/docs/9.1/static/datatype-money.html

Improve this answer
3
  • I finally decided to create a VIEW for the table, to case the column into a plain numeric. This seems to work for now. – Yanick Rochon Jan 15 '15 at 19:28
  • 5
    Never cast monetary values to/from floating point. You will lose precision and have subtle rounding errors. – Tobia Nov 30 '16 at 10:24
  • 1
    @Tobia in order to avoid that, the conversion must be done in two steps: from money to numeric and then to float. More info about that in Postgres docs: postgresql.org/docs/9.6/static/datatype-money.html. – shime Oct 2 '18 at 15:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.