Working with Money in Postgres

Wouldn’t it be awesome if money worked just like time in Postgres? You could store one canonical version of it, it worked across the globe? Well sadly, money is a whole different ball of wax. Though like time, money is part of most database implementations and I wanted to lay out some of the best practices I’ve gathered for working with money in Postgres.

I also have a tutorial up if you want to try this with Postgres running in a web browser.

Data types for money

Money

Postgres actually does have a money data type. This is not recommended because it doesn’t handle fractions of a cent and currency is tied to a database locale setting. While the money type isn’t best practice for storing money, I do think money is handy for casting data when you want your query output to be formatted like money.

Floats

Float numbers are often popular with any system using positive and negative numbers with decimals (since the name float means that the numbers float across the number scale). Float (real / float4) & double float datatypes (float8) could be used for money, but they generally aren’t ideal because they’re fundamentally imprecise.

So for example, this is correct:

select 0.0001::float4;

0.0001
(1 row)

And so is this:

select 0.0001::float4 + 0.0001::float4;

0.0002
(1 row)

But if we try to go out to additional fractions, this isn’t really the expected result:

select 0.0001::float4 + 0.0001::float4 + 0.0001::float4;

0.00029999999
(1 row)

Integers

Lots of folks use integer for storing money. Integers do not support any kind of decimals, so 100/3 might equal 33.3333, but in integer math that’s just 33. This can work for storing money if you know what your smallest unit is going to be (even down to fractions of a cent) and can use a multiplier in your database. So the multiplier would be 100 for dealing with a whole number of cents, or 1000000000 if you want to represent an amount like 0.237928372 BTC. This unit is stored whole, which solves the issues of float’s unrepresentable values.

There are some physical limitations with this technique, as integer can only store numbers up to 2147483647 and bigint can store only up to 9223372036854775807.

Integer is however notably performant and storage efficient. It's only a 4 byte sized column, 8 if you’re using bigint. Also, keep in mind, storing money as an integer will require division or casting to a different data type to output in a traditional format for your front end or sql reports to represent dollars, cents, or decimal numbers.

Numeric

numeric is widely considered the ideal datatype for storing money in Postgres. numeric and decimal are synonyms for each other, there's no difference in functionality between these two, but I hear numeric used more often in Postgres conversations. Numeric can go out to a lot of decimal places (10,000+ digits!!!) and you get to define the precision. The number data type has two qualifiers, the precision and scale to let you define a sensible number of decimal points to use.

When you create the type, it will look something like this NUMERIC(10,5) where precision is 10 and scale factor is 5.

• Precision is the total number of digits before and after the decimal point. You need to set this to the highest amount you ever might need to store. So here 99,999.9999 is the maximum and -99,999.9999 the minimum.
• Scale factor is the number of digits following your decimal, so this would be 5 decimal places.

Choosing a scale factor means that at some point Postgres will be rounding numbers. If you want to prevent rounding, make sure your scale number is really really high.

Compared to integer, number data types take up a lot of space, 10 bytes per column row. So if space and performance are a huge concern, and decimal precision is not, you might be better off with integer.

Storing money

Ok, so we have a data type to store actual cents, dollars, euros, etc. Now how do we store currency? In general it is best practice to store the currency alongside the number itself if you need to store money in multiple currencies at the same time. See ISO 4217 if you want the official currency codes. You can use a custom check constraint to require your data be entered for only certain currencies, for example, if you’re using dollars, pounds, and euros that might look like.

CREATE TABLE products (
    sku SERIAL PRIMARY KEY,
    name VARCHAR(255),
    price NUMERIC(7,5),
    currency TEXT CHECK (currency IN ('USD', 'EUR', 'GBP'))
);

If you’re working with currency in many formats there’s a lot to consider. In many cases, a lot of stuff will happen at the time of the transaction. Say a price set in the database in USD displayed to a user in GBP. You’d have a table like the above with a different table for a GBP exchange rate. Perhaps that table updates via API as currency values fluctuate throughout the day. You may have prices set in one currency and the price paid in a different one, entered with the amount paid at the time of purchase.

Functions for money

• Averages

and rounding to the nearest cent

SELECT ROUND(AVG(price), 2) AS truncated_average_price
FROM products;

• Rounding up with ceiling

totaling and rounding up to the nearest integer

SELECT CEIL(SUM(price)) AS rounded_total_price
FROM products;

• Rounding down with floor

totaling and rounding down to the nearest integer

SELECT FLOOR(SUM(price)) AS rounded_total_price
FROM products;

• Medians

Calculating the median can be a bit more involved because PostgreSQL doesn't have a built-in median function, but you can use window functions to calculate this

WITH sorted_prices AS (
    SELECT price,
           ROW_NUMBER() OVER (ORDER BY price) as r,
           COUNT(*) OVER () as total_count
    FROM products
)
SELECT FLOOR(AVG(price)) AS rounded_median_price
FROM sorted_prices
WHERE r IN (total_count / 2, (total_count + 1) / 2);

• Casting to the money type

If you’d like a result with a currency sign, commas, and periods.

SELECT CEIL(SUM(price))::money AS rounded_total_price_money
FROM products;

Note that the currency sign will appear based on your locale settings, show lc_monetary; will tell you what that is and you can update it to a different currency.

Summary

• Use int or bigint if you can work with whole numbers of cents and you don’t need fractional cents. This saves space and offers better performance. Store your money in cents and convert to a decimal on your output. This is also really the preferred method if all currency is the same type. If you’re changing currency often and dealing with fractional cents, move on to numeric.
• Use decimal / numeric for storing money in fractional cents and even out to many many decimal points. If you need to support lots of precision in money, this is the best method but there’s a bit of storage and performance cost.
• Store currency separately from the actual monetary values, so you can run calculations on currency conversions.