Monetary Type

The monetary type stores a numeric amount with its currency, preventing accidental mixing of different currencies.

Architecture

Basic Usage

SELECT '100 EUR'::monetary AS value;

Run Reset

Arithmetic

Add or subtract values with the same currency:

SELECT '100 EUR'::monetary + '50 EUR'::monetary AS sum;

Run Reset

Attempting to add different currencies raises an error:

SELECT '100 EUR'::monetary + '50 USD'::monetary AS error;

Run Reset

Currency Functions

Get the currency for a country:

SELECT currency_for_country('DE') AS germany, currency_for_country('US') AS usa, currency_for_country('JP') AS japan;

Run Reset

Get countries using a currency:

SELECT countries_for_currency('EUR') AS eurozone;

Run Reset

Parsing

The parser handles various formats:

SELECT '€100'::monetary AS symbol, '100 EUR'::monetary AS code, 'EUR 100.50'::monetary AS prefix;

Run Reset

Ambiguous symbols like $ require explicit currency codes:

SELECT '$100'::monetary AS ambiguous;

Run Reset