Horton
Menu

Thread

  1. insert column monetary type ver 2

    Дмитрий Иванов <firstdismay@gmail.com> — 2021-11-20T12:45:03Z

    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?

  2. Re: insert column monetary type ver 2

    Josef Šimánek <josef.simanek@gmail.com> — 2021-11-20T14:18:09Z

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

  3. Re: insert column monetary type ver 2

    Дмитрий Иванов <firstdismay@gmail.com> — 2021-11-20T14:32:54Z

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

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

  4. Re: insert column monetary type ver 2

    Adrian Klaver <adrian.klaver@aklaver.com> — 2021-11-20T16:13:13Z

    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

  5. Re: insert column monetary type ver 2

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-11-20T17:01:56Z

    =?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

  6. Re: insert column monetary type ver 2

    Ron Johnson <ronljohnsonjr@gmail.com> — 2021-11-21T04:02:29Z

    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.

  7. Re: insert column monetary type ver 2

    Дмитрий Иванов <firstdismay@gmail.com> — 2021-11-21T04:44:14Z

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

  8. Re: insert column monetary type ver 2

    Vincent Veyron <vincent.veyron@libremen.org> — 2021-11-21T15:46:48Z

    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

  9. Re: insert column monetary type ver 2

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-11-21T16:27:10Z

    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

  10. Re: insert column monetary type ver 2

    Ron Johnson <ronljohnsonjr@gmail.com> — 2021-11-22T01:08:59Z

    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.

  11. Re: insert column monetary type ver 2

    Adrian Klaver <adrian.klaver@aklaver.com> — 2021-11-22T01:33:58Z

    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

  12. Re: insert column monetary type ver 2

    Ron Johnson <ronljohnsonjr@gmail.com> — 2021-11-22T02:15:46Z

    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.

  13. insert column monetary type ver 2

    David G. Johnston <david.g.johnston@gmail.com> — 2021-11-22T02:43:22Z

    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.

  14. Re: insert column monetary type ver 2

    Ron Johnson <ronljohnsonjr@gmail.com> — 2021-11-22T03:21:53Z

    
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.

  15. Re: insert column monetary type ver 2

    Rob Sargent <robjsargent@gmail.com> — 2021-11-22T03:35:34Z

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

  16. Re: insert column monetary type ver 2

    Дмитрий Иванов <firstdismay@gmail.com> — 2021-11-22T04:28:39Z

    Thank you, I'm talking about this solution.

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

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

  17. Re: insert column monetary type ver 2

    Ron Johnson <ronljohnsonjr@gmail.com> — 2021-11-22T04:39:26Z

    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.

  18. Re: insert column monetary type ver 2

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-11-22T15:17:13Z

    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

  19. Re: insert column monetary type ver 2

    Дмитрий Иванов <firstdismay@gmail.com> — 2021-11-22T15:57:20Z

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

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

  20. Re: insert column monetary type ver 2

    Adrian Klaver <adrian.klaver@aklaver.com> — 2021-11-22T16:19:32Z

    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

  21. Re: insert column monetary type ver 2

    Tom Lane <tgl@sss.pgh.pa.us> — 2021-11-22T16:47:44Z

    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