How to set Postgresql money type to use 3 decimal places in Windows


Under Linux, type command in postgres database:

set lc_monetary to 'ar_BH.utf8';

ar_BH represents Arabic (Bahrain) locale, the above command change the locale monetary setting on-the-fly in postgresql database, then let do a test:

select 34.888::text::money;

You will get 34.888 with 3 decimal places money type. Tested in Postgresql 8.4.6.


However the above set command will get an error message under Windows.

FATAL: invalid value for parameter “lc_monetary”

You will need to:

set lc_monetary to "Arabic, Bahrain";

This will set the lc_monetary to arabic locale but only display 2 decimal places by default, it seems everything right of the comma is ignored.

To workaround this, use an ‘_’ in place of ‘, ‘ like:

set lc_monetary to "Arabic_Bahrain";

Tested with Windows XP + PostgreSQL 9.0.3(this solution should work for PostgreSQL 8.3 or later version too)

Thanks Jasen who got this resolved, more information please see his post @ postgresql forum:

USB backups under linux, changing dev names
How to configure public folder in Exchange server 2007