Thread: Invalid precision for money datatype
Hi,
We have a table created in Postgres database with money datatype and observed that the precision is shown as 2147483647 using a Java program. I am using Postgres driver jar postgresql-9.4-1203.jdbc42.jar and database version is 9.4.5.
I understand that the money datatype is locale specific and will not work similar to numeric columns, but we need the following information.
- How to get the locale for the money datatype column?
- How to get the precision and scale for the column given a locale?
Please answer the above questions.
Regards,
Kasyap
On Tuesday, May 8, 2018, SriRaghu Sai Kasyap <skasyap@opentext.com> wrote:
- How to get the locale for the money datatype column?
- How to get the precision and scale for the column given a locale?
As far as I know individual columns don't have locales. As for the money type, the stored value is strictly the number value. It is, for all intents and purposes, a numeric field with a more liberal input/constructor function.
You can inspect the locale information for the database (session?) though I'm not exactly sure how or how to change it. The documentation should cover this though.
David J.
>>>>> "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)