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, '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?

I'm not sure what's the problem on your side. But definitely check
this link https://wiki.postgresql.org/wiki/Don&#39;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.

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.

сб, 20 нояб. 2021 г. в 19:18, Josef Šimánek <josef.simanek@gmail.com>:

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.

I think I would love to discuss this topic, but my English won't allow it.
I understand this type is there but the best way to avoid mistakes when
working with it is not to work with it. thank you.

вс, 21 нояб. 2021 г. в 09:02, Ron <ronljohnsonjr@gmail.com>:

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 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.

David J.

On 11/21/21 8:43 PM, David G. Johnston wrote:

On Sunday, November 21, 2021, Ron <ronljohnsonjr@gmail.com
<mailto: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
<mailto: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.

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?

--
Angular momentum makes the world go 'round.

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

Thank you, I'm talking about this solution.

вс, 21 нояб. 2021 г. в 22:46, vincent.veyron@libremen.org <
vincent.veyron@libremen.org>:

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

About the locale, I wanted to add a couple of lines. I noticed a strange
behavior of the currency symbol.
The EDB installer, when specifying the Russian locale, sets the following
locale parameters:
(PostgreSQL 12.9, compiled by Visual C++ build 1914, 64-bit)
(Encoding UTF8)
lc_messages = 'Russian_Russia.1251' # locale for system error message
# strings
lc_monetary = 'Russian_Russia.1251' # locale for monetary formatting
lc_numeric = 'Russian_Russia.1251' # locale for number formatting
lc_time = 'Russian_Russia.1251' # locale for time formatting
In this case, the data of the financial type is not correctly displayed:
1 238,23 ?

If you set the locale:
lc_messages = 'ru_RU.UTF-8' # locale for system error message
# strings
lc_monetary = 'ru_RU.UTF-8' # locale for monetary formatting
lc_numeric = 'ru_RU.UTF-8' # locale for number formatting
lc_time = 'ru_RU.UTF-8' # locale for time formatting
The data of the financial type is displayed correctly:
1 238,23 ₽
--
Regards, Dmitry!

пн, 22 нояб. 2021 г. в 20:17, Tom Lane <tgl@sss.pgh.pa.us>:

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