Re: Invalid precision for money datatype

>>>>> "David" == David G Johnston <david.g.johnston@gmail.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)