Обсуждение: Invalid precision for money datatype

Поиск
Список
Период
Сортировка

Invalid precision for money datatype

От
SriRaghu Sai Kasyap
Дата:

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.

 

  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?

 

Please answer the above questions.

 

Regards,

Kasyap

 

Re: Invalid precision for money datatype

От
"David G. Johnston"
Дата:
On Tuesday, May 8, 2018, SriRaghu Sai Kasyap <skasyap@opentext.com> wrote:
  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?
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.

Re: Invalid precision for money datatype

От
Andrew Gierth
Дата:
>>>>> "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)