Postgresql: Which Datatype Should Be Used for Currency

PostgreSQL: Which Datatype should be used for Currency?

Numeric with forced 2 units precision. Never use float or float like datatype to represent currency because if you do, people are going to be unhappy when the financial report's bottom line figure is incorrect by + or - a few dollars.

The money type is just left in for historical reasons as far as I can tell.

Take this as an example: 1 Iranian Rial equals 0.000030 United States Dollars. If you use fewer than 5 fractional digits then 1 IRR will be rounded to 0 USD after conversion. I know we're splitting rials here, but I think that when dealing with money you can never be too safe.

Rails: Storing high currency values in PostgreSQL

I tried to use an integer field in my migration, however I get following error

PG::NumericValueOutOfRange: ERROR: numeric field overflow  
DETAIL: A field with precision 8, scale 2 must round to an absolute value less than 10^6.

That's a misunderstanding. The error message is for data type numeric - numeric(8,2) to be precise - not integer.

Solution

Just use numeric without precision and scale. It stores decimal number (with any amount of fractional digits) exactly as given.

decimal is a synonym of numeric in Postgres.

If you don't have fractional digits, consider integer (max 2^31 - 1) or bigint (max 2^63 - 1).

Related:

• PostgreSQL: Which Datatype should be used for Currency?

Which is the best practice for saving a price column on PostgreSQL?

Numeric is the best practice for storing prices.

In general, you want to avoid floating point representations for numbers because they can have rounding errors and precision errors -- that might add up over large quantities of numbers.

what data type should be used for storing a fixed monthly fee amount in postgres?

I wonder if the numeric type in my situation will have any advantages over the money type?

It's the other way round: the money type does not offer any advantages at all.

numeric is definitely the better choice.

See also don't use the money type in the Postgres Wiki

---

Related Topics