pg-currency

pg-currency copied to clipboard

README for currency contrib module

This postgres module implements an arbitrary precision, multi-currency money type.

• support for up to 999 currencies in a currency lookup table

• monetary values stored in the same amount of space as a NUMERIC, plus 2 bytes.

• convenient conversion between currencies

• addition of values in different currencies; nomination of a "neutral" currency via auxilliary table

• fast bulk comparisons (eg sorting a large result set by price), by internally caching exchange rates table

Defined Entities

TLA is defined as a basic type, represented as a 15-bit quantity internally (stored in an int2)

CURRENCY is also defined as a basic type, which wraps the "numeric" type and associates a TLA with it; the currency code.

CURRENCY_RATE is a defined lookup table; entries must be inserted into it before any values can be constructed.

This contains:

currency_code: a three-letter currency code

description: an arbitrary text comment for the code

symbol: prefix for display purposes (may be NULL, in which case the code is used as a suffix instead)

minor: for currencies which use a minor currency unit, the number of decimal points between the major and the minor currency unit. eg 2 for USD, EUR, 0 for currencies which don't use a minor currency unit. For divisible, high unit value currencies like gold etc, use 5 or higher.

rate: conversion rate to the neutral currency

is_exchange: boolean, set to 't' for the neutral currency - there MUST be exactly ONE neutral currency.

Supported Operations

The examples below assume that CURRENCY_RATE is pre-loaded with EUR and NZD and that EUR has a symbol defined, but NZD doesn't.

Conversion to/from text returns any 'useless' precision present:

'100EUR'::currency        =  '100 EUR'
'-100EUR'::currency       = '-100 EUR'
'+100.000EUR'::currency   =  '100.000 EUR'

Printing using relevant currency sign and precision:

format('100EUR'::currency) =   '€ 100.00'
#'100EUR'::currency        =   '€ 100.00'
#'100NZD'::currency        =   'NZD 100.00'

Get components out:

code('100EUR'::currency)  = 'EUR'::tla
value('100EUR'::currency)  = '100'::numeric

Valid ways to combine a currency code and a number:

currency(100, 'eur')       = '100 EUR'
(100::text || 'EUR')::currency

Conversion to a particular currency:

'100EUR'::currency->'USD' = '132.40 USD'::currency

Conversion to the 'exchange' or 'neutral' currency, you can simply cast as 'money';

'100EUR'::currency::money = '$600.00'::money

Otherwise, you have to use the 'particular currency' mechanism above.

Note that the 'money' type is limited to a 64-bit quantity of whatever the smallest unit of currency is.

Comparisons between currency values are supported; they need not be of the same currency type.

'100EUR'::currency > '100GBP'::currency
... ORDER BY my_currency_column;

Addition of units (assuming BTC as the 'exchange' currency, exchanging at 4 BTC = 1 USD is used a neutral currency):

'100EUR'::currency + '10USD'::currency = '5997.19 BTC'::currency

Other math on currency units:

'100EUR'::currency - '10EUR'::currency =  '90 EUR'::currency
'100EUR'::currency / 7     =  '14.28 EUR'::currency
'14.28EUR'::currency * 7   =  '99.96 EUR'::currency
'100EUR'::currency / 7 * 7 = '100.00 EUR'::currency (see below)

'100EUR'::currency / '10EUR' = 10::numeric

'100EUR'::currency + 10                => type error
'100EUR'::currency * '10EUR'::currency => type error

-'100EUR'::currency        = '-100 EUR'::currency
+'100EUR'::currency        = '100 EUR'::currency

Indexing

To support queries which might join by or sort by currency values, there are operator classes defined.

However, do not index currency values; the functions which back them are not IMMUTABLE, so if you create indexes with them then those indexes may not be able to retrieve your data or otherwise behave bizarrely. Postgres should notice this and refuse to create the index, but doesn't currently, so just beware.

Rounding

All decisions on precision and suchlike are punted on and given to the NUMERIC type underlying the values. To avoid lots of useless precision, use the format() functions.

Copyright and License

This contrib/ module is Copyright (c) 2010, 2011, Adioso Ltd. This module is free software; you may use it under the same terms as Postgres itself.