Monetary Type (PostgreSQL)

The PostgreSQL extension provides the same monetary type with identical semantics.

Installation

CREATE EXTENSION pg_monetary;

Basic Usage

SELECT '100 EUR'::monetary AS value;

Arithmetic

SELECT '100 EUR'::monetary + '50 EUR'::monetary AS sum;

Currency mismatch raises an error:

SELECT '100 EUR'::monetary + '50 USD'::monetary;
-- ERROR: Cannot add monetary values with different currencies

Currency Functions

SELECT currency_for_country('US');  -- USD
SELECT currency_for_country('DE');  -- EUR
SELECT countries_for_currency('EUR');

Aggregates

Sum monetary values:

SELECT SUM(amount::monetary)
FROM transactions
WHERE currency = 'EUR';

Comparison

SELECT '100 EUR'::monetary > '50 EUR'::monetary;  -- true

Comparing different currencies errors:

SELECT '100 EUR'::monetary > '50 USD'::monetary;
-- ERROR: Cannot compare monetary values with different currencies

Indexing

Create indexes on monetary columns:

CREATE INDEX idx_amount ON transactions (amount);

Migration from MONEY type

PostgreSQL’s built-in MONEY type is locale-dependent and problematic. Migrate:

ALTER TABLE orders
  ADD COLUMN amount_new monetary;

UPDATE orders
SET amount_new = (amount::numeric || ' ' || currency)::monetary;

ALTER TABLE orders
  DROP COLUMN amount,
  DROP COLUMN currency;

ALTER TABLE orders
  RENAME COLUMN amount_new TO amount;