Hi hackers,
During Nordic PGDay 2026, a group of us discussed various open problems,
and the money type was a heavily debated topic. We all agreed that
something needs to be done, since it's quite a terrible type for various
reasons. One idea that was suggested was to move it to contrib/ and try
to deprecate it. Instead of doing that, I suggest that we try to fix it.
One of the most frequent complaints about the money type is the lack of
currency exchange rate support.
PostgreSQL recently added a build option for libcurl, so adding support
for this now seems doable, and by caching the exchange rate per session,
users will not have to pay the cost for an FX lookup more than once per
session, per currency pair. This can smoothly be extended to support a
shared FX cache, with persistent storage on disk, to ensure FX rates
survive reconnections and restarts.
The freely available FX API from https://api.frankfurter.app/ is
currently hard-coded as a proof of concept, but this should of course
be made configurable via a new GUC, which should be a list of API URLs
for redundancy.
Here is an example that converts from USD to SEK:
joel@Mac postgresql % psql
psql (19devel)
Type "help" for help.
joel=# \timing
Timing is on.
joel=# SELECT 100::money AS usd;
usd
---------
$100.00
(1 row)
Time: 1.429 ms
joel=# SET money_source_currency = 'USD';
SET
Time: 0.530 ms
joel=# SET lc_monetary = 'sv_SE.UTF-8';
SET
Time: 0.906 ms
joel=# SELECT 100::money AS usd;
usd
-----------
949,67 kr
(1 row)
Time: 38.121 ms
joel=# SELECT 100::money AS usd;
usd
-----------
949,67 kr
(1 row)
Time: 0.324 ms
joel=#
As shown above, the FX lookup takes about 38 ms the first time, after
which the session cache makes subsequent lookups cheap.
/Joel
