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

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: http://postgresql.1045698.n5.nabble.com/win-Locales-quot-Arabic-Gum-quot-td3369213.html