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.
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
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 (
float4) & double float datatypes (
could be used for money, but they generally aren’t ideal because they’re
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)
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
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 is widely considered the ideal datatype for storing money in Postgres.
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
When you create the type, it will look something like this
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.
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.
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;
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
bigintif 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
numericfor 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.
October 11, 2023 •More by this author