Обсуждение: insert column monetary type ver 2

so 20. 11. 2021 v 13:45 odesílatel Дмитрий Иванов
<firstdismay@gmail.com> napsal:
>
> Good afternoon.
> Sorry about the first example.
> I can't figure out the problem.  Digit group group separator causes an insertion error, what should I do?
> lc_monetary = 'ru_RU.UTF-8'
> Digit group group separator is a space.
> Financial type format: 7,649.00 ₽
> DELETE FROM ONLY bpd.class_prop_user_small_val
> WHERE id_class_prop = 74502;
> Initial version of the data:
> INSERT INTO bpd. class_prop_user_small_val (id_class_prop, timestamp_class, val_int, val_boolean, val_varchar,
val_real,val_numeric, val_date, val_time, val_interval, val_timestamp, val_money, val_double, max_val, round, id_class,
id_data_type,inheritance, val_bigint, min_val, max_on, min_on, round_on) VALUES (74502, '2021-09-08 10: 05:12. 618',
NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '7 649.00 ₽', NULL, -1, 2, 6161, 6, false, 0, -1, false, false,
true);
> ----------
> ERROR: invalid input syntax for type money: "7 649,00 ₽" LINE 6: ..., NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
'7649,00 ... 
> Corrected
> INSERT INTO bpd. class_prop_user_small_val (id_class_prop, timestamp_class, val_int, val_boolean, val_varchar,
val_real,val_numeric, val_date, val_time, val_interval, val_timestamp, val_money, val_double, max_val, round, id_class,
id_data_type,inheritance, val_bigint, min_val, max_on, min_on, round_on) VALUES (74502, '2021-09-08 10: 05:12. 618',
NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '7649.00 ₽', NULL, -1, 2, 6161, 6, false, 0, -1, false, false,
true);
> -----------
> INSERT 0 1
>
> Why does a financial type conversion with a legal regional digit group separator cause an error?

I'm not sure what's the problem on your side. But definitely check
this link https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_money
explaining why money is not recommended to be used. Maybe you're
facing one of the known problems.

On 11/20/21 04:45, Дмитрий Иванов wrote:
> Good afternoon.
> Sorry about the first example.
> I can't figure out the problem.  Digit group group separator causes an 
> insertion error, what should I do?
> lc_monetary = 'ru_RU.UTF-8'
> Digit group group separator is a space.
> Financial type format: 7,649.00 ₽
> DELETE FROM ONLY bpd.class_prop_user_small_val
> WHERE id_class_prop = 74502;
> Initial version of the data:
> INSERT INTO bpd. class_prop_user_small_val (id_class_prop, 
> timestamp_class, val_int, val_boolean, val_varchar, val_real, 
> val_numeric, val_date, val_time, val_interval, val_timestamp, val_money, 
> val_double, max_val, round, id_class, id_data_type, inheritance, 
> val_bigint, min_val, max_on, min_on, round_on) VALUES (74502, 
> '2021-09-08 10: 05:12. 618', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
> NULL, NULL, '7 649.00 ₽', NULL, -1, 2, 6161, 6, false, 0, -1, false, 
> false, true);
> ----------
> ERROR: invalid input syntax for type money: "7 649,00 ₽" LINE 6: ..., 
> NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, '7 649,00 ...
> Corrected
> INSERT INTO bpd. class_prop_user_small_val (id_class_prop, 
> timestamp_class, val_int, val_boolean, val_varchar, val_real, 
> val_numeric, val_date, val_time, val_interval, val_timestamp, val_money, 
> val_double, max_val, round, id_class, id_data_type, inheritance, 
> val_bigint, min_val, max_on, min_on, round_on) VALUES (74502, 
> '2021-09-08 10: 05:12. 618', NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
> NULL, NULL, '7649.00 ₽', NULL, -1, 2, 6161, 6, false, 0, -1, false, 
> false, true);
> -----------
> INSERT 0 1
> 
> Why does a financial type conversion with a legal regional digit group 
> separator cause an error?

Because this(cash.c):


https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/utils/adt/cash.c;h=d093ce80386f4cf61f3127f3cfe77181f4edfed5;hb=HEAD

has this?:

/* cash_in()
   91  * Convert a string to a cash data type.
   92  * Format is [$]###[,]###[.##]
   93  * Examples: 123.45 $123.45 $123,456.78
   94  *
   95  */

The 1000s(group) separator is determined by LC_NUMERIC and I don't 
believe that is used by the money type.

-- 
Adrian Klaver
adrian.klaver@aklaver.com

=?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay@gmail.com> writes:
> I can't figure out the problem.  Digit group group separator causes an
> insertion error, what should I do?
> lc_monetary = 'ru_RU.UTF-8'

On my RHEL8 (moderately recent glibc) platform, that locale's
mon_thousands_sep symbol is not a plain space but "\342\200\257":

p *lconvert
$3 = {decimal_point = 0x23ef120 ".", thousands_sep = 0x23ef140 "",
  grouping = 0x23ef160 "", int_curr_symbol = 0x23ef1a0 "RUB ",
  currency_symbol = 0x23ef1c0 "\342\202\275",
  mon_decimal_point = 0x23ef1e0 ",",
  mon_thousands_sep = 0x23ef200 "\342\200\257",
  mon_grouping = 0x23ef220 "\003\003", positive_sign = 0x23ef240 "",
  negative_sign = 0x23ef260 "-", int_frac_digits = 2 '\002',
  frac_digits = 2 '\002', p_cs_precedes = 0 '\000', p_sep_by_space = 1 '\001',
  n_cs_precedes = 0 '\000', n_sep_by_space = 1 '\001', p_sign_posn = 1 '\001',
  n_sign_posn = 1 '\001', int_p_cs_precedes = 0 '\000',
  int_p_sep_by_space = 0 '\000', int_n_cs_precedes = 0 '\000',
  int_n_sep_by_space = 0 '\000', int_p_sign_posn = 0 '\000',
  int_n_sign_posn = 0 '\000'}

A quick lookup later, that's U+202F or "narrow no-break space".

cash_in is picky about this, and won't take plain ASCII space as
a substitute.  Not sure if it should.

            regards, tom lane

On 11/20/21 11:01 AM, Tom Lane wrote:
> =?UTF-8?B?0JTQvNC40YLRgNC40Lkg0JjQstCw0L3QvtCy?= <firstdismay@gmail.com> writes:
>> I can't figure out the problem.  Digit group group separator causes an
>> insertion error, what should I do?
>> lc_monetary = 'ru_RU.UTF-8'
> On my RHEL8 (moderately recent glibc) platform, that locale's
> mon_thousands_sep symbol is not a plain space but "\342\200\257":
>
> p *lconvert
> $3 = {decimal_point = 0x23ef120 ".", thousands_sep = 0x23ef140 "",
>    grouping = 0x23ef160 "", int_curr_symbol = 0x23ef1a0 "RUB ",
>    currency_symbol = 0x23ef1c0 "\342\202\275",
>    mon_decimal_point = 0x23ef1e0 ",",
>    mon_thousands_sep = 0x23ef200 "\342\200\257",
>    mon_grouping = 0x23ef220 "\003\003", positive_sign = 0x23ef240 "",
>    negative_sign = 0x23ef260 "-", int_frac_digits = 2 '\002',
>    frac_digits = 2 '\002', p_cs_precedes = 0 '\000', p_sep_by_space = 1 '\001',
>    n_cs_precedes = 0 '\000', n_sep_by_space = 1 '\001', p_sign_posn = 1 '\001',
>    n_sign_posn = 1 '\001', int_p_cs_precedes = 0 '\000',
>    int_p_sep_by_space = 0 '\000', int_n_cs_precedes = 0 '\000',
>    int_n_sep_by_space = 0 '\000', int_p_sign_posn = 0 '\000',
>    int_n_sign_posn = 0 '\000'}
>
> A quick lookup later, that's U+202F or "narrow no-break space".
>
> cash_in is picky about this, and won't take plain ASCII space as
> a substitute.  Not sure if it should.

It probably should, based on the Robustness Principle: "be conservative in 
what you send, be liberal in what you accept".

-- 
Angular momentum makes the world go 'round.

On Sat, 20 Nov 2021 19:32:54 +0500
Дмитрий Иванов <firstdismay@gmail.com> wrote:

> I saw it. I’m not tied down hard on this type. It's just that it exists and
> I decided to include it in my development. Thanks.
>


Consider storing integers; it's easy and fast, and all you need to do is display your numbers divided by 100, using
to_char(): 

select to_char(100001/100::numeric(10,2), '999G999D99');
   to_char
-------------
    1 000,01


--
https://marica.fr
Logiciel de gestion des contentieux juridiques, des contrats et des sinistres d'assurance
                    Bien à vous, Vincent Veyron

Ron <ronljohnsonjr@gmail.com> writes:
> On 11/20/21 11:01 AM, Tom Lane wrote:
>> A quick lookup later, that's U+202F or "narrow no-break space".
>> cash_in is picky about this, and won't take plain ASCII space as
>> a substitute.  Not sure if it should.

> It probably should, based on the Robustness Principle: "be conservative in 
> what you send, be liberal in what you accept".

The contrary argument is that people tend not to like "best guess"
behavior when working with monetary amounts.

            regards, tom lane

On 11/21/21 10:27 AM, Tom Lane wrote:
> Ron <ronljohnsonjr@gmail.com> writes:
>> On 11/20/21 11:01 AM, Tom Lane wrote:
>>> A quick lookup later, that's U+202F or "narrow no-break space".
>>> cash_in is picky about this, and won't take plain ASCII space as
>>> a substitute.  Not sure if it should.
>> It probably should, based on the Robustness Principle: "be conservative in
>> what you send, be liberal in what you accept".
> The contrary argument is that people tend not to like "best guess"
> behavior when working with monetary amounts.

Best Guess is relative.  Breaking because of "ASCII Space" instead of 
"narrow no-break space" is definitely Not Right.

-- 
Angular momentum makes the world go 'round.

On 11/21/21 17:08, Ron wrote:
> On 11/21/21 10:27 AM, Tom Lane wrote:
>> Ron <ronljohnsonjr@gmail.com> writes:

>> The contrary argument is that people tend not to like "best guess"
>> behavior when working with monetary amounts.
> 
> Best Guess is relative.  Breaking because of "ASCII Space" instead of 
> "narrow no-break space" is definitely Not Right.

The people I have worked for have forgiven me many sins, messing up the 
money is not one of them.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 11/21/21 7:33 PM, Adrian Klaver wrote:
> On 11/21/21 17:08, Ron wrote:
>> On 11/21/21 10:27 AM, Tom Lane wrote:
>>> Ron <ronljohnsonjr@gmail.com> writes:
>
>>> The contrary argument is that people tend not to like "best guess"
>>> behavior when working with monetary amounts.
>>
>> Best Guess is relative.  Breaking because of "ASCII Space" instead of 
>> "narrow no-break space" is definitely Not Right.
>
> The people I have worked for have forgiven me many sins, messing up the 
> money is not one of them.

Not inserting this record is also messing up the money.


-- 
Angular momentum makes the world go 'round.

On 11/21/21 7:33 PM, Adrian Klaver wrote:

On 11/21/21 17:08, Ron wrote:

On 11/21/21 10:27 AM, Tom Lane wrote:

Ron <ronljohnsonjr@gmail.com> writes:

The contrary argument is that people tend not to like "best guess"
behavior when working with monetary amounts.

Best Guess is relative.  Breaking because of "ASCII Space" instead of "narrow no-break space" is definitely Not Right.

The people I have worked for have forgiven me many sins, messing up the money is not one of them.

Not inserting this record is also messing up the money.

On Sunday, November 21, 2021, Ron <ronljohnsonjr@gmail.com> wrote:

On 11/21/21 7:33 PM, Adrian Klaver wrote:

On 11/21/21 17:08, Ron wrote:

On 11/21/21 10:27 AM, Tom Lane wrote:

Ron <ronljohnsonjr@gmail.com> writes:

The contrary argument is that people tend not to like "best guess"
behavior when working with monetary amounts.

Best Guess is relative.  Breaking because of "ASCII Space" instead of "narrow no-break space" is definitely Not Right.

The people I have worked for have forgiven me many sins, messing up the money is not one of them.

Not inserting this record is also messing up the money.

Adding processing smarts here is Not Definitely Right.  And the status quo is Not Definitely Wrong.  As long as we don’t have a round-trip problem I’d lean toward maintaining the status quo and accept the complaints that we aren’t being as flexible as we could be with our money type.  That we interpret the LC setting exactly is a defensible position to take.

Especially since money is a type we advise people avoid anyway.  We can just add this to the list of reasons why.

> On Nov 21, 2021, at 8:21 PM, Ron <ronljohnsonjr@gmail.com> wrote:
>
> Why then does the money type exist?  At the very least, why isn't it deprecated?

Some of us like it as an easy formatter trick for reports
>
> --
> Angular momentum makes the world go 'round.

On 11/21/21 9:35 PM, Rob Sargent wrote:
>
>> On Nov 21, 2021, at 8:21 PM, Ron <ronljohnsonjr@gmail.com> wrote:
>>
>> Why then does the money type exist?  At the very least, why isn't it deprecated?
> Some of us like it as an easy formatter trick for reports

to_char() can do the same, no?

-- 
Angular momentum makes the world go 'round.

Ron <ronljohnsonjr@gmail.com> writes:
> On 11/21/21 9:35 PM, Rob Sargent wrote:
>> Some of us like it as an easy formatter trick for reports

> to_char() can do the same, no?

to_char doesn't have adequate logic for locale-specific monetary formatting.
You can get it to emit a locale-specific currency symbol, but it has no
clue whether that should go before or after the value.  It knows nothing
of other locale-specific details, such as possibly using parens in place
of a minus sign.  Also, the POSIX API allows monetary decimal point and
thousands separators to be different from the numeric ones that to_char
knows about.  (I have no idea which locales use that, but I doubt they'd
have put in that complication without need.)

            regards, tom lane

On 11/21/21 19:21, Ron wrote:
> 
> 

> 
> Which means *that* is broken.
> 
>> Especially since money is a type we advise people avoid anyway.  We 
>> can just add this to the list of reasons why.
> 
> Why then does the money type exist?  At the very least, why isn't it 
> deprecated?

It was at one point:

https://www.postgresql.org/docs/8.2/datatype-money.html

Not entirely sure why it was revived. You can search the mailing list 
archive for discussions on this.

Bottom line is if you don't want surprises use numeric and format the 
output on the client side.

> 
> -- 
> Angular momentum makes the world go 'round.


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 11/21/21 19:21, Ron wrote:
>> Why then does the money type exist?  At the very least, why isn't it 
>> deprecated?

> It was at one point:

> https://www.postgresql.org/docs/8.2/datatype-money.html

> Not entirely sure why it was revived. You can search the mailing list 
> archive for discussions on this.

I think we concluded there was no appetite for actually removing it.
8.3 fixed the very worst problem that had led to deprecation, namely
making it an 8-byte integer not 4-byte, so we concluded that it had
at least some excuse to live.  As already noted, if it doesn't do
what you want, don't use it.

            regards, tom lane