Crunchy Bridge for Analytics now has support for Iceberg and other new features. Learn more in our Crunchy Bridge for Analytics announcement.

Tutorial Instructions

# Working with Money in Postgres

## 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;
``````

And so is this:

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

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;
``````

### 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 still major limitations with this technique, as `integer` can only store numbers up to 2147483647 and `bigint` can store only up to 9223372036854775807.

Integer is notably performance and storage efficient. Its only a 4 byte sized column, 8 if you’re using `bigint`. Also, keep in mind, storing money as an integer will be 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` / `decimal` is widely considered the ideal datatype for storing money in Postgres. You 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(7,5)` where precision is 7 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.99999 is the maximum and -99.99999 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 no, 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.

This is what has been loaded into this tutorial.

``````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 set up like 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 `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 bet but there’s a bit of storage and performance cost here.
• Store currency separately from the actual monetary values, so you can run calculations on currency conversions.