Monetary Types

Introduction to the MONEY Type

The money type in PostgreSQL is designed to store currency values with fixed fractional precision.

Syntax

amount MONEY

It stores values like $123.45, depending on your locale.

Creating a Table with MONEY Type

CREATE TABLE salaries (
  id SERIAL PRIMARY KEY,
  employee_name TEXT,
  monthly_salary MONEY
);

INSERT INTO salaries (employee_name, monthly_salary) VALUES
('Alice', '$2500.00'),
('Bob', '$3000.50'),
('Charlie', '$2750.25');

Querying and Arithmetic with MONEY

-- Add bonus of $200 to each salary
SELECT employee_name, monthly_salary + '$200' AS salary_with_bonus
FROM salaries;

-- Annual salary
SELECT employee_name, monthly_salary * 12 AS annual_salary
FROM salaries;

Locale Formatting of MONEY

The format of money depends on the lc_monetary setting.

SHOW lc_monetary;

Create database with specific locale:

createdb salary_db --lc-monetary='de_DE.UTF-8' --template=template0

In SQL:

CREATE DATABASE salary_db
  WITH TEMPLATE = template0
  LC_MONETARY = 'de_DE.UTF-8'
  ENCODING = 'UTF8';

Converting MONEY to Numeric

-- Convert for calculations
SELECT employee_name, monthly_salary::NUMERIC * 0.18 AS tax
FROM salaries;

Best Practices

• Use money for fixed, simple currency storage.
• Use NUMERIC for more control and currency conversion.
• Format output using TO_CHAR() when needed:

SELECT employee_name, TO_CHAR(monthly_salary, 'FM999999.00') AS formatted_salary
FROM salaries;

Limitations of MONEY Type

• Limited to a single currency symbol
• Locale-specific formatting may be confusing in multi-region apps
• Does not support multi-currency storage cleanly

Alternatives to MONEY Type

Prefer NUMERIC(10,2) with controlled formatting:

CREATE TABLE invoices (
  id SERIAL PRIMARY KEY,
  client TEXT,
  total_amount NUMERIC(10, 2)
);

INSERT INTO invoices (client, total_amount) VALUES
('Client A', 12345.67),
('Client B', 98765.43);

SELECT client, TO_CHAR(total_amount, 'FM999,999.00') AS formatted_total
FROM invoices;

Decimal and Thousands Separator

The default locale en_US.UTF-8 uses dot . as decimal and comma , as thousands separator.

To use comma as decimal (European style):

createdb euro_db --lc-monetary='de_DE.UTF-8' --template=template0