[TEST REPORT] 9.1Alpha3 Feature E.1.4.7.2 in release notes.

[TEST REPORT]

[Release]: 9.1Alpha3. Binaries compiled with mingw-32 (gcc 4.4.0) on
i686 without zlib support.

[Test Type]: Feature

[Test]: a) Check feature E.1.4.7.2 in 9.1Alpha3 release notes
(Monetary data type). b) Documentation mistake(?)

[Platform]: Windows 7 Professional 64 bit. Intel Core i5 2.67 GHz. 4 GB RAM.

[Parameters]: None

[Failure]: Yes

[Results]: Documentation states that integer literals are allowed
values for input. I am getting the following error:

test=# select version();
PostgreSQL 9.1alpha3 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
4.4.0, 32-bit
(1 row)

test=# show lc_monetary;
Japanese_Japan.932
(1 row)

test-# CREATE TABLE moneytbl VALUES (m1 money, m2 money);
CREATE TABLE
test=# INSERT INTO moneytbl VALUES (1,2);
ERROR: column "m1" is of type money but expression is of type integer
at character 30
HINT: You will need to rewrite or cast the expression.
STATEMENT: INSERT INTO moneytbl VALUES (1,2);
ERROR: column "m1" is of type money but expression is of type integer
LINE 1: INSERT INTO moneytbl VALUES (1,2);
^
HINT: You will need to rewrite or cast the expression.
test-#

[Comments]:
1. Other type of inserts seem fine so far:
test=# INSERT INTO moneytbl VALUES ('1','2');
INSERT 0 1
test=# INSERT INTO moneytbl VALUES ('\1',\'2'); -- The "\" character
translates to the yen symbol in a Windows japanese locale environment.
INSERT 0 1
test=# INSERT INTO moneytbl VALUES (1::numeric,2::numeric); -- Feature
numeric to money cast check. OK.
INSERT 0 1
test=# INSERT INTO moneytbl VALUES (10.4345,7.234);
INSERT 0 1
test=# SELECT *, m1/m2 INTO moneytbl VALUES (10.4345,7.234); --
Feature division of monetary types yielding float8. OK.
m1 | m2 |
-----+----+------------------
\1 | \2 | 0.5
\1 | \2 | 0.5
\1 | \2 | 0.5
\10 | \7 | 1.42857142857143

2. A nitpick is that fraction yen values cannot be saved. float8
inputs in ja_JP locale settings get rounded (as seen above). While the
lowest denomination is indeed 1円, fractional yen values are highly
desirable. I am forced to use numeric (x,2) as fractional yen values
are not supported. This is based on a production use-case scenario.

Errata:
> test=# SELECT *, m1/m2 INTO moneytbl VALUES (10.4345,7.234); --
should read as:
test=# SELECT *, m1/m2 FROM moneytbl;

[TEST REPORT]

[Release]: 9.1Alpha3. Binaries compiled with mingw-32 (gcc 4.4.0) on
i686 without zlib support.

[Test Type]: Feature

[Test]: a) Check feature E.1.4.7.2 in 9.1Alpha3 release notes
(Monetary data type). b) Documentation mistake(?)

[Platform]: Windows 7 Professional 64 bit. Intel Core i5 2.67 GHz. 4 GB RAM.

[Parameters]: None

[Failure]: Yes

[Results]: Documentation states that integer literals are allowed
values for input. I am getting the following error:

test=# select version();
PostgreSQL 9.1alpha3 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
4.4.0, 32-bit
(1 row)

test=# show lc_monetary;
Japanese_Japan.932
(1 row)

test-# CREATE TABLE moneytbl VALUES (m1 money, m2 money);
CREATE TABLE
test=# INSERT INTO moneytbl VALUES (1,2);
ERROR: column "m1" is of type money but expression is of type integer
at character 30
HINT: You will need to rewrite or cast the expression.
STATEMENT: INSERT INTO moneytbl VALUES (1,2);
ERROR: column "m1" is of type money but expression is of type integer
LINE 1: INSERT INTO moneytbl VALUES (1,2);
                                                              ^
HINT: You will need to rewrite or cast the expression.
test-#

[Comments]:
1. Other type of inserts seem fine so far:
test=# INSERT INTO moneytbl VALUES ('1','2');
INSERT 0 1
test=# INSERT INTO moneytbl VALUES ('\1',\'2');   -- The "\" character
translates to the yen currency symbol in a Windows japanese locale environment.
INSERT 0 1
test=# INSERT INTO moneytbl VALUES (1::numeric,2::numeric); -- Feature
numeric to money cast check. OK.
INSERT 0 1
test=# INSERT INTO moneytbl VALUES (10.4345,7.234);
INSERT 0 1
test=# SELECT *, m1/m2 FROM moneytbl; --
Feature division of monetary types yielding float8. OK.
 m1  | m2 |
-----+----+------------------
 \1 | \2 |              0.5
 \1 | \2 |              0.5
 \1 | \2 |              0.5
 \10 | \7 | 1.42857142857143

2. A nitpick is that fraction yen values cannot be saved. float8
inputs in ja_JP locale settings get rounded (as seen above). While the
lowest denomination is indeed 1円, fractional yen values are highly
desirable. I am forced to use numeric (x,2) as fractional yen values
are not supported. This is based on a production use-case scenario.

Ramanujam

On Fri, Jan 7, 2011 at 15:54, Ramanujam <innomotive(at)gmail(dot)com> wrote:
> [Release]: 9.1Alpha3. Binaries compiled with mingw-32 (gcc 4.4.0) on
> i686 without zlib support.

> [Test]: a) Check feature E.1.4.7.2 in 9.1Alpha3 release notes
> (Monetary data type). b) Documentation mistake(?)
>
> [Results]: Documentation states that integer literals are allowed
> values for input. I am getting the following error:

The docs is:
http://developer.postgresql.org/pgdocs/postgres/datatype-money.html
| Input is accepted in a variety of formats,
| including integer and floating-point literals

The reported issue doesn't depend on lc_monetary.
It comes from missing cast support from integer to money.

Should we have cast to/from integer to numeric? It is inconsistent
that 1::numeric::money is accepted but 1::money is not.

postgres=# SHOW lc_monetary;
lc_monetary
-------------
C
(1 row)

postgres=# SELECT 1::numeric::money;
money
-------
$1.00
(1 row)

postgres=# SELECT 1::integer::money;
ERROR: cannot cast type integer to money
LINE 1: SELECT 1::integer::money;
^
postgres=# SELECT castsource::regtype, casttarget::regtype,
castfunc::regproc, castcontext FROM pg_cast WHERE casttarget =
'money'::regtype;
castsource | casttarget | castfunc | castcontext
------------+------------+----------+-------------
numeric | money | money | a
(1 row)

postgres=# \df money
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+-------+------------------+---------------------+--------
pg_catalog | money | money | numeric | normal
(1 row)

--
Itagaki Takahiro

It was reported from a tester that we don't have casts of money from/to integer
types even though we have from/to numeric type.

http://archives.postgresql.org/pgsql-testers/2011-01/msg00000.php

Did we have any discussions about the behavior?
I think we should have them for consistency.

---------- Forwarded message ----------
From: Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com>
Date: Fri, Jan 7, 2011 at 16:34
Subject: Re: [TESTERS] [TEST REPORT] 9.1Alpha3 Feature E.1.4.7.2 in
release notes.
To: Ramanujam <innomotive(at)gmail(dot)com>
Cc: pgsql-testers(at)postgresql(dot)org

On Fri, Jan 7, 2011 at 15:54, Ramanujam <innomotive(at)gmail(dot)com> wrote:
> [Release]: 9.1Alpha3. Binaries compiled with mingw-32 (gcc 4.4.0) on
> i686 without zlib support.

> [Test]: a) Check feature E.1.4.7.2 in 9.1Alpha3 release notes
> (Monetary data type). b) Documentation mistake(?)
>
> [Results]: Documentation states that integer literals are allowed
> values for input. I am getting the following error:

The docs is:
http://developer.postgresql.org/pgdocs/postgres/datatype-money.html
| Input is accepted in a variety of formats,
| including integer and floating-point literals

The reported issue doesn't depend on lc_monetary.
It comes from missing cast support from integer to money.

Should we have cast to/from integer to numeric?  It is inconsistent
that 1::numeric::money is accepted but 1::money is not.

postgres=# SHOW lc_monetary;
 lc_monetary
-------------
 C
(1 row)

postgres=# SELECT 1::numeric::money;
 money
-------
 $1.00
(1 row)

postgres=# SELECT 1::integer::money;
ERROR:  cannot cast type integer to money
LINE 1: SELECT 1::integer::money;
                        ^
postgres=# SELECT castsource::regtype, casttarget::regtype,
castfunc::regproc, castcontext FROM pg_cast WHERE casttarget =
'money'::regtype;
 castsource | casttarget | castfunc | castcontext
------------+------------+----------+-------------
 numeric    | money      | money    | a
(1 row)

postgres=# \df money
                         List of functions
  Schema   | Name  | Result data type | Argument data types |  Type
------------+-------+------------------+---------------------+--------
 pg_catalog | money | money            | numeric             | normal
(1 row)

--
Itagaki Takahiro

Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com> writes:
> It was reported from a tester that we don't have casts of money from/to integer
> types even though we have from/to numeric type.

In most locales, the idea isn't sensible.

regards, tom lane

On Tue, Jan 11, 2011 at 11:10, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com> writes:
>> It was reported from a tester that we don't have casts of money from/to integer
>> types even though we have from/to numeric type.
>
> In most locales, the idea isn't sensible.

The documentation says:
| Input is accepted in a variety of formats,
| including integer and floating-point literals

If we won't to add accept integers for money, we should fix the docs.
| integer and floating-point string literals
| ~~~~~~~~~~~~~~~
Will it get better?

--
Itagaki Takahiro

On tis, 2011-01-11 at 12:30 +0900, Itagaki Takahiro wrote:
> On Tue, Jan 11, 2011 at 11:10, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com> writes:
> >> It was reported from a tester that we don't have casts of money from/to integer
> >> types even though we have from/to numeric type.
> >
> > In most locales, the idea isn't sensible.
>
> The documentation says:
> | Input is accepted in a variety of formats,
> | including integer and floating-point literals
>
> If we won't to add accept integers for money, we should fix the docs.
> | integer and floating-point string literals
> | ~~~~~~~~~~~~~~~
> Will it get better?

I think adding a cast from integer to money is probably quite
reasonable. The other way around, maybe not, or only an explicit cast.

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> On tis, 2011-01-11 at 12:30 +0900, Itagaki Takahiro wrote:
>> If we won't to add accept integers for money, we should fix the docs.
>> | integer and floating-point string literals
>> | ~~~~~~~~~~~~~~~
>> Will it get better?

> I think adding a cast from integer to money is probably quite
> reasonable. The other way around, maybe not, or only an explicit cast.

As near as I can tell, this entire thread started because someone
thought that the reference to "numeric" in the release notes implied
"any numerical type", not "the type named numeric". We explicitly
rejected the idea of providing direct casts to/from floating point
types, on the grounds of not wanting any roundoff error; so I don't
think this is a point that should be revisited. Perhaps it would be
sufficient to clarify the release-note item.

regards, tom lane

On tis, 2011-01-11 at 11:03 -0500, Tom Lane wrote:
> We explicitly
> rejected the idea of providing direct casts to/from floating point
> types, on the grounds of not wanting any roundoff error; so I don't
> think this is a point that should be revisited.

We also explicitly chose floating point as the result of the money/money
operator over numeric. Seems a bit inconsistent.