Currency Conversions

The extension provides multiple ways to convert monetary values between currencies - from simple one-liners to full control over exchange rates.

Quick Start

-- Load extension
INSTALL monetary FROM community;
LOAD monetary;

-- Load exchange rates
CREATE TABLE exchange_rates AS SELECT * FROM ecb_exchange_rates();

-- Configure the rates table
SET monetary_exchange_table = 'exchange_rates';

-- Convert!
SELECT '100 EUR'::monetary ->> 'USD' AS in_usd;

Setup: Loading Exchange Rates

Before converting, you need exchange rates. The simplest approach:

-- Create rates table from ECB (free, no API key)
CREATE TABLE exchange_rates AS
SELECT * FROM ecb_exchange_rates();

-- Tell the extension which table to use
SET monetary_exchange_table = 'exchange_rates';

For persistent configuration, add to your ~/.duckdbrc:

SET monetary_exchange_table = 'exchange_rates';

Conversion Methods

Arrow Operator (->>)

The cleanest syntax for conversions:

-- EUR to USD
SELECT '100 EUR'::monetary ->> 'USD';
-- Result: 109.50 USD (rate dependent)

-- USD to EUR
SELECT '100 USD'::monetary ->> 'EUR';
-- Result: 91.32 EUR

-- GBP to JPY
SELECT '100 GBP'::monetary ->> 'JPY';
-- Result: 19,234 JPY

Works with table columns:

CREATE TABLE prices (product VARCHAR, price monetary);
INSERT INTO prices VALUES
    ('Widget', '29.99 EUR'),
    ('Gadget', '49.99 EUR'),
    ('Gizmo', '99.99 EUR');

SELECT product, price, price ->> 'USD' AS price_usd
FROM prices;

Currency Macros

Shorthand functions for common currencies:

-- Convert to specific currencies
SELECT USD('100 EUR'::monetary);  -- To US Dollars
SELECT EUR('100 USD'::monetary);  -- To Euros
SELECT GBP('100 EUR'::monetary);  -- To British Pounds
SELECT JPY('100 EUR'::monetary);  -- To Japanese Yen
SELECT CHF('100 EUR'::monetary);  -- To Swiss Francs

All available macros:

Region	Currencies
Major	EUR, USD, GBP, JPY, CHF
Europe	NOK, SEK, DKK, PLN, CZK, HUF, RON
Asia-Pacific	CNY, KRW, SGD, HKD, TWD, INR, THB, IDR, MYR, PHP, AUD, NZD
Americas	CAD, BRL, MXN, CLP, COP, ARS
Middle East/Africa	AED, SAR, ILS, TRY, ZAR, EGP
Crypto	BTC, ETH
Precious Metals	XAU, XAG

convert_to Function

Programmatic conversion with variable target:

-- Same as arrow operator
SELECT convert_to('100 EUR'::monetary, 'USD');

-- Useful with variables
SELECT convert_to(price, target_currency)
FROM prices, (SELECT 'GBP' as target_currency);

Direct Conversion with Explicit Rate

When you have the rate already:

-- monetary_convert(value, target_currency, rate)
SELECT monetary_convert('100 USD'::monetary, 'EUR', 0.92);
-- Result: 92.00 EUR

-- Useful for custom rates or specific point-in-time rates
SELECT monetary_convert(
    '1000 EUR'::monetary,
    'USD',
    1.0850  -- Your specific rate
);

Conversion with Markup/Spread

Apply a percentage spread (common in FX trading):

-- monetary_convert_markup(value, target, rate, spread_pct)
-- Spread is subtracted: effective_rate = rate * (1 - spread/100)

SELECT monetary_convert_markup('1000 EUR'::monetary, 'USD', 1.10, 2.0);
-- Rate becomes: 1.10 * 0.98 = 1.078
-- Result: 1078.00 USD

Real-World Examples

E-commerce: Multi-Currency Product Catalog

-- Products with base EUR prices
CREATE TABLE products (
    id INTEGER,
    name VARCHAR,
    price monetary
);

INSERT INTO products VALUES
    (1, 'Basic Plan', '9.99 EUR'),
    (2, 'Pro Plan', '29.99 EUR'),
    (3, 'Enterprise', '99.99 EUR');

-- Show prices in multiple currencies
SELECT
    name,
    price AS eur,
    price ->> 'USD' AS usd,
    price ->> 'GBP' AS gbp,
    price ->> 'JPY' AS jpy
FROM products;

Financial Report: Convert Revenue by Region

CREATE TABLE revenue (
    region VARCHAR,
    amount monetary,
    quarter VARCHAR
);

INSERT INTO revenue VALUES
    ('Europe', '1500000 EUR', 'Q1'),
    ('Americas', '2000000 USD', 'Q1'),
    ('Asia', '180000000 JPY', 'Q1'),
    ('UK', '800000 GBP', 'Q1');

-- Total revenue in USD
SELECT
    SUM(monetary_decimal(amount ->> 'USD')) AS total_usd
FROM revenue;

-- By region in USD
SELECT
    region,
    amount,
    amount ->> 'USD' AS amount_usd
FROM revenue
ORDER BY monetary_decimal(amount ->> 'USD') DESC;

Travel App: Currency Converter

-- User's budget in their home currency
WITH budget AS (
    SELECT '5000 USD'::monetary AS amount
)
SELECT
    amount AS original,
    amount ->> 'EUR' AS europe,
    amount ->> 'GBP' AS uk,
    amount ->> 'JPY' AS japan,
    amount ->> 'THB' AS thailand,
    amount ->> 'MXN' AS mexico
FROM budget;

Trading: Apply Different Spreads

-- Different spreads for different customer tiers
CREATE TABLE fx_orders (
    customer_tier VARCHAR,
    amount monetary,
    target_currency VARCHAR
);

INSERT INTO fx_orders VALUES
    ('retail', '10000 EUR', 'USD'),
    ('premium', '50000 EUR', 'USD'),
    ('institutional', '1000000 EUR', 'USD');

-- Get base rate
WITH base_rate AS (
    SELECT rate FROM exchange_rates
    WHERE currency_from = 'EUR' AND currency_to = 'USD'
    ORDER BY valid_from DESC LIMIT 1
)
SELECT
    customer_tier,
    amount,
    CASE customer_tier
        WHEN 'retail' THEN monetary_convert_markup(amount, 'USD', rate, 3.0)
        WHEN 'premium' THEN monetary_convert_markup(amount, 'USD', rate, 1.5)
        WHEN 'institutional' THEN monetary_convert_markup(amount, 'USD', rate, 0.5)
    END AS converted
FROM fx_orders, base_rate;

Historical Conversion: Point-in-Time Rates

-- Store rates with timestamps
CREATE TABLE historical_rates AS
SELECT * FROM ecb_exchange_rates();

-- Convert using rate from specific date
WITH rate_on_date AS (
    SELECT rate FROM historical_rates
    WHERE currency_from = 'EUR'
      AND currency_to = 'USD'
      AND valid_from <= '2025-01-15'
    ORDER BY valid_from DESC
    LIMIT 1
)
SELECT monetary_convert('1000 EUR'::monetary, 'USD', rate)
FROM rate_on_date;

Aggregate Conversions

Sum with Conversion

Convert each row then sum:

-- Sum all revenue in USD
CREATE TABLE sales (amount monetary);
INSERT INTO sales VALUES
    ('100 EUR'), ('200 EUR'), ('150 GBP'), ('10000 JPY');

SELECT monetary_sum(amount ->> 'USD') AS total_usd
FROM sales;

monetary_sum_converted Aggregate

For performance with large datasets:

-- Requires rate for each row
WITH sales_with_rates AS (
    SELECT
        s.amount,
        r.rate
    FROM sales s
    JOIN exchange_rates r ON
        monetary_currency(s.amount) = r.currency_from::VARCHAR
        AND r.currency_to = 'USD'
)
SELECT monetary_sum_converted(amount, 'USD', rate) AS total
FROM sales_with_rates;

Conversion Logic

The extension uses these rules for EUR-based rate tables:

Conversion	Logic
Same currency	Returns unchanged
EUR → X	Multiply by rate
X → EUR	Divide by rate
X → Y	Cross-rate: (Y_rate / X_rate)

Example cross-rate calculation:

-- GBP to JPY via EUR
-- If EUR/GBP = 0.85 and EUR/JPY = 160
-- Then GBP/JPY = 160 / 0.85 = 188.24

SELECT '100 GBP'::monetary ->> 'JPY';
-- Uses cross-rate automatically

Configuration

Set Exchange Table

-- Use a specific table for rate lookups
SET monetary_exchange_table = 'my_custom_rates';

-- Check current setting
SELECT current_setting('monetary_exchange_table');

Required Table Schema

Your exchange rate table needs these columns:

Column	Type	Required
currency_from	VARCHAR/ENUM	Yes
currency_to	VARCHAR/ENUM	Yes
rate	DECIMAL/DOUBLE	Yes
valid_from	TIMESTAMP	Yes

The ->> operator automatically selects the most recent rate by valid_from.

Error Handling

-- Missing rate throws error
SELECT '100 XYZ'::monetary ->> 'USD';
-- Error: No exchange rate found for XYZ to USD

-- Currency mismatch in arithmetic still errors
SELECT '100 EUR'::monetary + '50 USD'::monetary;
-- Error: Cannot add monetary values with different currencies

-- Convert first, then add
SELECT ('100 EUR'::monetary ->> 'USD') + '50 USD'::monetary;
-- Works: 159.50 USD