monetary

Features

Type-Safe Money

Store monetary values with their currency. Arithmetic between different currencies throws errors - no silent bugs.

Real-time Exchange Rates

10 built-in sources: ECB, Bank of Canada, RBA, Swiss National Bank, and more. 7 free, 3 paid.

70+ Currencies

All major fiat currencies, precious metals (XAU, XAG), and crypto (BTC, ETH) with correct decimal precision.

Easy Conversions

Convert with arrow operator: price ->> 'USD' or macros: USD(price), EUR(price).

Quick Install

INSTALL monetary FROM community;
LOAD monetary;

Basic Usage

-- Parse monetary values
SELECT '99.99 USD'::monetary AS price;
SELECT '$49.99'::monetary AS price;      -- Symbol parsing
SELECT '€100'::monetary AS price;         -- Euro symbol

-- Safe arithmetic (same currency only)
SELECT '100 EUR'::monetary + '50 EUR'::monetary;  -- 150.00 EUR
SELECT '100 EUR'::monetary * 2;                    -- 200.00 EUR

-- This throws an error (different currencies)
SELECT '100 EUR'::monetary + '50 USD'::monetary;
-- Error: Cannot add monetary values with different currencies

Load Exchange Rates

-- Fetch rates from European Central Bank (free)
CREATE TABLE exchange_rates AS
SELECT * FROM ecb_exchange_rates();

-- See what we got
SELECT currency_to, rate
FROM exchange_rates
WHERE currency_from = 'EUR'
LIMIT 5;

Convert Currencies

-- Configure which table has rates
SET monetary_exchange_table = 'exchange_rates';

-- Convert using arrow operator
SELECT '100 EUR'::monetary ->> 'USD' AS in_dollars;

-- Or use currency macros
SELECT USD('100 EUR'::monetary) AS in_dollars;
SELECT GBP('100 EUR'::monetary) AS in_pounds;
SELECT JPY('100 EUR'::monetary) AS in_yen;

Multi-Currency Reports

-- Products priced in EUR, shown in multiple currencies
CREATE TABLE products (name VARCHAR, price monetary);
INSERT INTO products VALUES
    ('Basic', '9.99 EUR'),
    ('Pro', '29.99 EUR'),
    ('Enterprise', '99.99 EUR');

SELECT
    name,
    price AS eur,
    price ->> 'USD' AS usd,
    price ->> 'GBP' AS gbp
FROM products;

Country to Currency Lookup

-- Get currency for any country
SELECT currency_for_country('US');  -- USD
SELECT currency_for_country('JP');  -- JPY
SELECT currency_for_country('DE');  -- EUR

-- Get all countries using a currency
SELECT countries_for_currency('EUR');
-- ['DE', 'FR', 'IT', 'ES', 'NL', 'BE', 'AT', ...]

Exchange Rate Sources

Source	API Key	Base Currency	Update
ECB	No	EUR	Daily
Bank of Canada	No	CAD	Daily
Reserve Bank of Australia	No	AUD	Daily
Norges Bank	No	NOK	Daily
Czech National Bank	No	CZK	Daily
Swiss National Bank	No	CHF	Daily
FreeGoldAPI	No	USD (Gold)	Daily
Twelve Data	Yes	USD	Real-time
Open Exchange Rates	Yes	USD	Hourly
GoldAPI	Yes	USD	Real-time

Ready to Start?

Installation Guide

Install for DuckDB or PostgreSQL →

Exchange Rates

Fetch rates from 10 sources →

Currency Conversions

Convert between currencies →

All Functions

Complete function reference →