PostgreSQL Money Type: Definition, Usage, and Examples

In PostgreSQL, the money type is a special data type designed to store currency values with fixed fractional precision. It automatically formats numbers as monetary amounts, including currency symbols and decimal places, making it useful for financial data storage and calculations.

How It Works

The money type in PostgreSQL stores currency values as fixed-point numbers with two decimal places by default. Think of it like a wallet that keeps track of your cash exactly, including cents, without rounding errors that can happen with floating-point numbers.

When you insert or query a money value, PostgreSQL formats it with a currency symbol and thousands separators based on your locale settings. This makes it easy to read and understand amounts as real money, similar to how prices appear on receipts or price tags.

Internally, PostgreSQL stores money values as 64-bit integers representing the amount in the smallest currency unit (like cents). This ensures accuracy in calculations like addition or subtraction, which is important for financial applications.

Example

This example shows how to create a table with a money column, insert values, and select them to see the formatted output.

sql

CREATE TABLE sales (
  id SERIAL PRIMARY KEY,
  product_name TEXT,
  price MONEY
);

INSERT INTO sales (product_name, price) VALUES
('Coffee', 3.50),
('Sandwich', 7.25);

SELECT id, product_name, price FROM sales;

Output

 id | product_name | price 
----+--------------+-------
  1 | Coffee       | $3.50 
  2 | Sandwich     | $7.25 

When to Use

Use the money type when you need to store and display currency amounts with fixed decimal precision and automatic formatting. It is ideal for financial applications like billing systems, invoices, or sales records where accuracy and clear presentation of money values matter.

However, if you need to handle multiple currencies or require more complex calculations, consider using numeric types with explicit scale and precision instead, as money is limited to fixed formatting and locale-dependent display.

Key Points

• Fixed precision: stores currency with two decimal places by default.
• Automatic formatting: displays values with currency symbols and separators.
• Accurate calculations: avoids floating-point rounding errors.
• Locale-dependent: output format depends on server locale settings.
• Limited flexibility: not ideal for multi-currency or complex financial calculations.

Key Takeaways

The money type stores currency values with fixed two-decimal precision and formats them with currency symbols.

It is best for simple financial data where accurate calculations and readable output are needed.

Money values are stored internally as integers representing the smallest currency unit to avoid rounding errors.

Locale settings affect how money values are displayed, including currency symbol and separators.

For multi-currency or advanced financial needs, consider numeric types instead of money.