Re: Invalid precision for money datatype

>>>>> "David" == David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> writes:

>> 1. How to get the locale for the money datatype column?
>> 2. How to get the precision and scale for the column given a locale?

David> As far as I know individual columns don't have locales. As for
David> the money type, the stored value is strictly the number value.
David> It is, for all intents and purposes, a numeric field with a more
David> liberal input/constructor function.

David> You can inspect the locale information for the database
David> (session?) though I'm not exactly sure how or how to change it.
David> The documentation should cover this though.

The locale used is the per-session value of lc_monetary, but there's no
provided explicit SQL interface to query the scale factor used. However
you can get that by doing scale('0'::money::numeric) which gives the
value for the current locale:

# set lc_monetary = 'en_US.UTF-8'; -- USD
# select scale('0'::money::numeric), 0::money;
scale | money
-------+-------
2 | $0.00

# set lc_monetary = 'hy_AM.UTF-8'; -- Armenian dram
# select scale('0'::money::numeric), 0::money;
scale | money
-------+-------
0 | ֏ 0

# set lc_monetary = 'ar_JO.UTF-8'; -- Jordanian dinar
# select scale('0'::money::numeric), 0::money::text;
scale | text
-------+-----------------
3 | د.أ.‏ 0.000

Using the money type is a really bad idea; usually much better to stick
to numeric.

--
Andrew (irc:RhodiumToad)