Functions (PostgreSQL)

The PostgreSQL extension provides functions for creating, manipulating, and querying monetary values.

Constructor Functions

make_monetary (integer)

Create a monetary value from cents/minor units:

-- From cents
SELECT make_monetary(12345, 'EUR');
-- Result: 123.45 EUR

-- Zero-decimal currencies store whole units
SELECT make_monetary(100, 'JPY');
-- Result: 100 JPY

make_monetary (numeric)

Create from a decimal amount with proper rounding:

-- From decimal (uses banker's rounding)
SELECT make_monetary(123.456, 'USD');
-- Result: 123.46 USD

SELECT make_monetary(123.445, 'USD');
-- Result: 123.44 USD (banker's rounding)

Accessor Functions

monetary_amount

Get the raw integer amount (minor units):

SELECT monetary_amount('99.99 EUR'::monetary);
-- Result: 9999

monetary_currency

Get the currency code:

SELECT monetary_currency('100 USD'::monetary);
-- Result: USD

monetary_decimal

Get the decimal representation:

SELECT monetary_decimal('99.99 EUR'::monetary);
-- Result: 99.99

Country/Currency Functions

currency_for_country

Get the primary currency for a country (ISO 3166-1 alpha-2 or alpha-3):

SELECT currency_for_country('US');   -- USD
SELECT currency_for_country('USA');  -- USD
SELECT currency_for_country('DE');   -- EUR
SELECT currency_for_country('JP');   -- JPY
SELECT currency_for_country('GB');   -- GBP

countries_for_currency

Get all countries using a currency:

SELECT countries_for_currency('EUR');
-- Result: {AD,AT,BE,CY,DE,EE,ES,FI,FR,GR,IE,IT,LT,LU,LV,MC,ME,MT,NL,PT,SI,SK,SM,VA,XK}

SELECT countries_for_currency('USD');
-- Result: {AS,EC,FM,GU,IO,MH,MP,PR,PW,SV,TC,UM,US,VG,VI}

Arithmetic Operators

Addition (+)

Same currency only:

SELECT '100 EUR'::monetary + '50 EUR'::monetary;
-- Result: 150.00 EUR

-- Different currencies throw error:
SELECT '100 EUR'::monetary + '50 USD'::monetary;
-- ERROR: Cannot add monetary values with different currencies (EUR vs USD)

Subtraction (-)

SELECT '100 EUR'::monetary - '30 EUR'::monetary;
-- Result: 70.00 EUR

Negation (-)

SELECT -('100 EUR'::monetary);
-- Result: -100.00 EUR

Multiplication (*)

Multiply by integer, numeric, or float:

SELECT '100 EUR'::monetary * 3;
-- Result: 300.00 EUR

SELECT '100 EUR'::monetary * 1.5;
-- Result: 150.00 EUR

SELECT 2 * '50 USD'::monetary;
-- Result: 100.00 USD

Division (/)

Divide by scalar:

SELECT '100 EUR'::monetary / 4;
-- Result: 25.00 EUR

SELECT '100 EUR'::monetary / 3;
-- Result: 33.33 EUR (uses banker's rounding)

Divide monetary by monetary to get ratio:

SELECT '150 EUR'::monetary / '50 EUR'::monetary;
-- Result: 3.0

Comparison Operators

All comparisons require matching currencies:

SELECT '100 EUR'::monetary > '50 EUR'::monetary;   -- true
SELECT '100 EUR'::monetary < '50 EUR'::monetary;   -- false
SELECT '100 EUR'::monetary = '100 EUR'::monetary;  -- true
SELECT '100 EUR'::monetary <> '50 EUR'::monetary;  -- true
SELECT '100 EUR'::monetary >= '100 EUR'::monetary; -- true
SELECT '100 EUR'::monetary <= '100 EUR'::monetary; -- true

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

Aggregate Functions

SUM

Sum monetary values (must be same currency):

SELECT SUM(amount) FROM orders WHERE currency = 'EUR';
-- Result: monetary sum

-- Mixed currencies throw error:
SELECT SUM(amount) FROM orders;
-- ERROR: cannot sum EUR and USD: currency mismatch in aggregate

AVG

Average with proper rounding:

SELECT AVG(amount) FROM orders WHERE currency = 'EUR';

MIN / MAX

Find minimum or maximum (same currency only):

SELECT MIN(amount), MAX(amount)
FROM orders
WHERE currency = 'EUR';

Type Casting

Text to monetary

SELECT '100 EUR'::monetary;
SELECT '€99.99'::monetary;
SELECT 'USD 50'::monetary;
SELECT '$49.99'::monetary;

Numeric to monetary

Use make_monetary for explicit conversion:

SELECT make_monetary(price, 'USD') FROM products;

Indexing

Create indexes on monetary columns for efficient queries:

-- B-tree index for comparisons
CREATE INDEX idx_amount ON orders(amount);

-- For range queries
SELECT * FROM orders
WHERE amount > '100 EUR'::monetary
  AND amount < '500 EUR'::monetary;

Function Summary

Function	Input	Output	Description
make_monetary(int, text)	BIGINT, VARCHAR	monetary	From minor units
make_monetary(num, text)	NUMERIC, VARCHAR	monetary	From decimal
monetary_amount(m)	monetary	BIGINT	Get minor units
monetary_currency(m)	monetary	TEXT	Get currency code
monetary_decimal(m)	monetary	NUMERIC	Get decimal value
currency_for_country(cc)	TEXT	TEXT	Country to currency
countries_for_currency(cc)	TEXT	TEXT[]	Currency to countries