monetary - Type-Safe Money for DuckDB

•70+ Currencies•10 Exchange Rate Sources

Why DECIMAL Fails for Money

Storing money as DECIMAL(10,2) causes real bugs:

-- Mixing currencies silently corrupts data
SELECT SUM(amount) FROM transactions;  -- 💥 USD + EUR + JPY = ???

-- Japanese Yen has no decimals, Kuwaiti Dinar has 3
INSERT INTO prices VALUES (100.50, 'JPY');  -- Wrong! JPY has 0 decimals

-- Currency symbols are ambiguous
SELECT '$100';  -- USD? CAD? AUD? SGD?

The monetary Type Solution

DuckDB

-- Currency is part of the type - mixing throws an error SELECT '100 USD'::monetary + '50 EUR'::monetary;

Run Reset

DuckDB

-- Correct decimals per currency, automatic parsing SELECT '¥10000'::monetary AS yen, '100.500 KWD'::monetary AS dinar, '0.00000001 BTC'::monetary AS satoshi;

Run Reset

Install in 10 Seconds

INSTALL monetary FROM community;
LOAD monetary;

Tip

Works with DuckDB 1.0+ on Linux, macOS, and Windows. PostgreSQL support planned for Q1 2026.

Key Features

Type-Safe Arithmetic

Adding EUR + USD throws an error instead of silently corrupting your data. Catch currency bugs at query time, not in production.

Smart Parsing

Understands $99.99, €100, ¥10000, 100 USD, and USD 100. Rejects ambiguous symbols like S$ with helpful errors.

Correct Precision

JPY/KRW: 0 decimals. USD/EUR: 2 decimals. KWD/BHD: 3 decimals. BTC: 8 decimals. All handled automatically.

10 Rate Sources

ECB, Bank of Canada, RBA, Norges Bank, CNB, SNB, and more. 7 free sources, 3 paid for real-time data.

Try It Live

These examples run in your browser using DuckDB WASM:

Note

Live examples work best on desktop. On mobile, view the code snippets and try them in your local DuckDB.

DuckDB

-- Parse monetary values SELECT '99.99 USD'::monetary AS from_code, '$49.99'::monetary AS from_symbol, '€100'::monetary AS euro;

Run Reset

DuckDB

-- Safe arithmetic SELECT '100 EUR'::monetary + '50 EUR'::monetary AS sum, '100 EUR'::monetary * 1.5 AS scaled, '100 EUR'::monetary / 3 AS divided;

Run Reset

DuckDB

-- Country to currency lookup SELECT currency_for_country('US') AS usa, currency_for_country('JP') AS japan, currency_for_country('KW') AS kuwait;

Run Reset

Currency Conversion

-- 1. Load exchange rates (free, no API key)
CREATE TABLE exchange_rates AS
SELECT * FROM ecb_exchange_rates();

-- 2. Configure the extension
SET monetary_exchange_table = 'exchange_rates';

-- 3. Convert with arrow operator or macros
SELECT '100 EUR'::monetary ->> 'USD' AS arrow_syntax,
       USD('100 EUR'::monetary) AS macro_syntax;

Real-World Example

-- E-commerce: prices in local currency, reports in USD
CREATE TABLE orders (
    id INTEGER,
    customer_country VARCHAR,
    amount monetary
);

INSERT INTO orders VALUES
    (1, 'DE', '99.99 EUR'),
    (2, 'JP', '15000 JPY'),
    (3, 'GB', '79.99 GBP');

-- Convert everything to USD for reporting
-- Note: Requires exchange_rates table configured
SELECT id, amount, amount ->> 'USD' AS amount_usd
FROM orders;

-- Safe conversion with fallback for missing rates
SELECT id, amount,
    TRY(amount ->> 'USD') AS amount_usd,
    CASE WHEN TRY(amount ->> 'USD') IS NULL
         THEN 'Rate unavailable' END AS status
FROM orders;

Exchange Rate Sources

Source	API Key	Base	Update	Best For
ECB	Free	EUR	Daily	EU businesses
Bank of Canada	Free	CAD	Daily	Canadian businesses
RBA	Free	AUD	Daily	Australian businesses
Norges Bank	Free	NOK	Daily	Nordic businesses
Czech National Bank	Free	CZK	Daily	Czech businesses
Swiss National Bank	Free	CHF	Daily	Swiss businesses
FreeGoldAPI	Free	XAU	Daily	Precious metals
Twelve Data	Paid	Any	Real-time	Trading apps
Open Exchange Rates	Paid	USD	Hourly	Global apps
GoldAPI	Paid	XAU	Real-time	Precious metals trading

Comparison: monetary vs DECIMAL

Feature	DECIMAL(10,2)	monetary
Currency stored with value	❌ Separate column	✅ Built-in
Prevents mixing currencies	❌ Silent corruption	✅ Throws error
Correct decimals per currency	❌ Manual	✅ Automatic
Parse $99 or €100	❌ Manual	✅ Built-in
Exchange rate conversion	❌ Manual joins	✅ price ->> 'USD'
Ambiguous symbol detection	❌ No	✅ Helpful errors

Next Steps

Use Cases

See real-world examples for e-commerce, finance, and crypto.

View Use Cases →

Exchange Rates

Set up automatic rate fetching from 10 different sources.

Configure Rates →

All Functions

Complete reference for all functions, operators, and aggregates.

Function Reference →

Double-Entry Ledgers

Build accounting systems with currency-validated transactions.

Ledger Patterns →