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

DuckDB

-- Load exchange rates (using sample data for demo) CREATE TABLE exchange_rates AS SELECT 'EUR' as currency_from, 'USD' as currency_to, 1.085 as rate, NOW() as valid_from UNION ALL SELECT 'EUR', 'GBP', 0.845, NOW() UNION ALL SELECT 'EUR', 'JPY', 162.5, NOW() UNION ALL SELECT 'EUR', 'CHF', 0.945, NOW(); -- Configure the rates table SET monetary_exchange_table = 'exchange_rates'; -- Convert EUR to USD SELECT '100 EUR'::monetary ->> 'USD' AS in_usd;

Run Reset

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:

DuckDB

-- Try different conversions (uses sample rates from Quick Start) SELECT '100 EUR'::monetary ->> 'USD' AS eur_to_usd, '100 EUR'::monetary ->> 'GBP' AS eur_to_gbp, '100 EUR'::monetary ->> 'JPY' AS eur_to_jpy;

Run Reset

Works with table columns:

DuckDB

-- Create products table with EUR prices 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;

Run Reset

Currency Macros

Shorthand functions for common currencies:

DuckDB

-- Convert to specific currencies using macros SELECT USD('100 EUR'::monetary) AS usd, GBP('100 EUR'::monetary) AS gbp, JPY('100 EUR'::monetary) AS jpy, CHF('100 EUR'::monetary) AS chf;

Run Reset

All available macros (each has an _AT variant for historical rates):

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

For historical conversions, use the _AT suffix: USD_AT(price, date), EUR_AT(price, date), etc.

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

Use the _AT suffix macros to convert using rates from a specific date:

-- Convert using rate closest to a specific date
SELECT USD_AT('1000 EUR'::monetary, '2025-01-15');
-- Uses the rate closest to January 15, 2025

SELECT GBP_AT('500 USD'::monetary, '2025-01-10');
-- Uses the GBP rate closest to January 10, 2025

-- Generic function with variable currency
SELECT convert_at_date('1000 EUR'::monetary, 'USD', '2025-01-15');

-- Or with variable target
SELECT convert_to_at(price, target_currency, report_date)
FROM transactions;

The _AT macros find the rate closest to the specified date. By default, an error is thrown if the closest rate is more than 24 hours away:

-- Configure max staleness (default: 86400 seconds = 24 hours)
SET monetary_max_rate_staleness = 604800;  -- 1 week

-- Now rates up to 7 days away are acceptable
SELECT USD_AT('1000 EUR'::monetary, '2025-01-15');

Available _AT macros for all currencies: EUR_AT, USD_AT, GBP_AT, JPY_AT, CHF_AT, CAD_AT, AUD_AT, and more.

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');

Set Max Rate Staleness

Controls how far from the requested date a rate can be when using _AT macros:

-- Default: 24 hours (86400 seconds)
SET monetary_max_rate_staleness = 86400;

-- Allow rates up to 1 week old
SET monetary_max_rate_staleness = 604800;

-- Require exact date match (within 1 hour)
SET monetary_max_rate_staleness = 3600;

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