4

I'm thinking of converting the numeric columns of my PG 9.2 database schema to money, mainly because of the benefits I see (or imagine?) in having rounding and formatting handled at the DB level, instead of the application level. Would there be some downsides to that decision?

cjauvin
  • 3,433
  • 4
  • 29
  • 38
  • 1
    ["Output ... depends on the locale"](http://www.postgresql.org/docs/current/interactive/datatype-money.html) so you might have *interesting* surprises. I tend to use `numeric` for currency but I'll let one of the experts say more. – mu is too short Apr 09 '13 at 01:52
  • 1
    I just discovered about `numeric`'s `precision` and `scale`: I might use that instead of `money`. – cjauvin Apr 09 '13 at 02:04
  • 1
    I often find it to be useful to create a `DOMAIN` pseudo-type for the precision and scale I want then use that everywhere. It's still `numeric` so it behaves sanely, but it's consistent. Explicit rounding is generally wise. – Craig Ringer Apr 09 '13 at 05:02

2 Answers2

10

Stick with Numeric:

  1. It is faster from what I have heard

  2. Output is consistent. What it means is consistent. Questions of whether to enforce rounding at storage or at retrieval (or in the application) are left to you. There are many cases where in fact you may want to store fractional cents and numeric lets you do this safely.

The other thing is that if you ever want to add multicurrency support, you will find numeric is far better at this than money is. This is because the currency symbol depends on your locale so you can insert $100, change your locale and have it show 100￥ which is not likely to be the same amount barring a massive economic shakeup.

I do financial accounting software. We made a decision to stick with numeric and we have been very happy with it.

Chris Travers
  • 25,424
  • 6
  • 65
  • 182
  • 1
    Just a quick update, #1 is wrong, the main advantage of the money type is performance. Saying that, there r quite a bunch of issues with it, for detailed response check http://stackoverflow.com/a/15729573/227225 – Nimrod Yonatan Ben-Nes Dec 01 '14 at 08:49
3

I am migrating MySQL to PostgreSQL. I decide to use 'Number' instead of 'Money' because :- 1) Money is local sensitive that mean it's impossible having 2 currency by default if we need. 2) Numeric can be cast to Money if I want it function as money.

Wutikrai
  • 39
  • 3