Introducing Crunchy Data Warehouse: A next-generation Postgres-native data warehouse. Crunchy Data Warehouse Learn more
Tutorial Instructions
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.
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;
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 / 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.
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.
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.
and rounding to the nearest cent
SELECT ROUND(AVG(price), 2) AS truncated_average_price
FROM products;totaling and rounding up to the nearest integer
SELECT CEIL(SUM(price)) AS rounded_total_price
FROM products;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);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.
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. 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. Loading terminal...
Loading terminal...