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, 2015 at 18:42
  • 2
    The MONEY datatype in Postgres is for dollars only. ;)
    – ypercubeᵀᴹ
    Jan 15, 2015 at 18:55
  • 1
    @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, 2015 at 19:02
  • I find money a rather useless datatype. numeric is much better and more flexible.
    – user1822
    Jan 15, 2015 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, 2016 at 10:29

1 Answer 1

Reset to default
12

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, 2015 at 19:28
  • 6
    Never cast monetary values to/from floating point. You will lose precision and have subtle rounding errors.
    – Tobia
    Nov 30, 2016 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, 2018 at 15:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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