money or dollar type

"Jose' Soares Da Silva" wrote:

I think that PostgreSQL money type should be very useful if we could
remove the dollar sign. We can't use it with Lira/Peseta/Mark etc.
In europe now we have Euro. If we remove the $ it will be useful otherwise
we have to rename it to 'dollar'. ;-)

Compile with LANG support and set the LANG environment variable for the
postmaster. Restart the postmaster.

Then you get your own currency symbol:

junk=> select * from moneybag;
who|amount
---+-------
A |£250.00
(1 row)

But I don't like the fact that this has to be done in the backend. It
means that the currency of money is tied to the LANG environment of the
postmaster, rather than to the data itself. One of the characteristics of
money is the currency in which it is denominated; this ought to be part
of the datatype. It would then be invalid to perform arithmetical
operations between different currencies, which would correctly reflect
the real world.

Therefore, I propose that the money type be extended to include a
currency definition, the default being that of the backend environment.
--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"Search me, O God, and know my heart; try me, and know
my thoughts. And see if there be any wicked way in me,
and lead me in the way everlasting."
Psalms 139:23,24

I think that PostgreSQL money type should be very useful if we could
remove the dollar sign. We can't use it with Lira/Peseta/Mark etc.
In europe now we have Euro. If we remove the $ it will be useful
otherwise we have to rename it to 'dollar'. ;-)

Have you tried compiling with "USE_LOCALE" turned on and with the right
setting for LC_xxx? The code is supposed to use local conventions, but I
don't know if it works in the way you want. I agree that it should...

PS: Is there a reason to left justify it ?

That is just an artifact of the column formatting; all columns are left
justified in psql afaik.

- Tom

"Oliver Elphick" <olly@lfix.co.uk> writes:

Therefore, I propose that the money type be extended to include a
currency definition, the default being that of the backend environment.

This is not a bad idea; it would address some problems that I have in
my application too. (What I was planning to do was store a separate
currency field associated with every money amount, but I think Oliver's
idea is better.)

However, what money *really* needs is more precision. Has there been
any thought of working on the full SQL exact-numeric package? (If I
read what I've seen correctly, that boils down to user-specifiable
decimal field widths, right?) A variable-width money type including
a currency indicator would actually solve my problem...

regards, tom lane

"Jose' Soares Da Silva" wrote:

On Mon, 11 May 1998, Oliver Elphick wrote:

"Jose' Soares Da Silva" wrote:

I think that PostgreSQL money type should be very useful if we could
remove the dollar sign. We can't use it with Lira/Peseta/Mark etc.
In europe now we have Euro. If we remove the $ it will be useful otherw

ise

we have to rename it to 'dollar'. ;-)

Compile with LANG support and set the LANG environment variable for the
postmaster. Restart the postmaster.

Then you get your own currency symbol:

What's happening with EURO sign?

I guess that will need a tweak to libc to support it - I wonder if the
glibc developers have thought about it?

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"Search me, O God, and know my heart; try me, and know
my thoughts. And see if there be any wicked way in me,
and lead me in the way everlasting."
Psalms 139:23,24

On Mon, 11 May 1998, Thomas G. Lockhart wrote:

I think that PostgreSQL money type should be very useful if we could
remove the dollar sign. We can't use it with Lira/Peseta/Mark etc.
In europe now we have Euro. If we remove the $ it will be useful
otherwise we have to rename it to 'dollar'. ;-)

Have you tried compiling with "USE_LOCALE" turned on and with the right
setting for LC_xxx? The code is supposed to use local conventions, but I
don't know if it works in the way you want. I agree that it should...

Thanks Tom, I will try it.

PS: Is there a reason to left justify it ?

That is just an artifact of the column formatting; all columns are left
justified in psql afaik.

^^^^^
Sorry Tom, I can't find the word 'afaik' on my dictionary.

Any way, seems that psql justify numbers to the right and text to the left,
money is numeric then I expect that psql justify it to the right.
It has also a little problem justifying varchars, look:

prova=> select var as my_varchar from prova where var = '12';
my_varchar
----------
12 <--right justified
(1 row)

prova=> select var as my_varchar from prova;
my_varchar
----------
12 <--left justified, this time ???
a12
a12
(3 rows)
Jose'

On Mon, 11 May 1998, Oliver Elphick wrote:

"Jose' Soares Da Silva" wrote:

I think that PostgreSQL money type should be very useful if we could
remove the dollar sign. We can't use it with Lira/Peseta/Mark etc.
In europe now we have Euro. If we remove the $ it will be useful otherwise
we have to rename it to 'dollar'. ;-)

Compile with LANG support and set the LANG environment variable for the
postmaster. Restart the postmaster.

Then you get your own currency symbol:

What's happening with EURO sign?

junk=> select * from moneybag;
who|amount
---+-------
A |�250.00
(1 row)

But I don't like the fact that this has to be done in the backend. It
means that the currency of money is tied to the LANG environment of the
postmaster, rather than to the data itself. One of the characteristics of
money is the currency in which it is denominated; this ought to be part
of the datatype. It would then be invalid to perform arithmetical
operations between different currencies, which would correctly reflect
the real world.

Therefore, I propose that the money type be extended to include a
currency definition, the default being that of the backend environment.

I agree, currently we can have only one currency definition. We can't
have for example Dollars and Pesetas in the same database.
Jose'

However, what money *really* needs is more precision. Has there been
any thought of working on the full SQL exact-numeric package?

Yes. The problem is that afaik there is no variable-width exact numeric
package available. BCD arithmetic could work if a package were
available. The GNU extended precision package looks interesting, but we
would have to translate from a string to internal format for every
operation, or somehow store the internal representation in each tuple
which seems messy.

I'm thinking of moving the 64-bit integer contrib package I wrote into
the native backend as a foundation for the numeric/decimal data types.
We would need to get feedback from more of the supported platforms on
how to do 64-bit integers (a few processors have them as a "long" type,
and the GNU 32-bit compilers seem to allow a "long long" declaration,
but I don't know what other systems do for this).

The only other thing which would need to be handled is how to pass along
the two value precision/scale parameters which are a part of the
declaration for these types. I've just finished working on the type
conversion algorithms so understand the current "atttypmod" field a bit
better, but have not decided how to extend it to multiple fields.

- Tom

I've just finished working on the type
conversion algorithms so understand the current "atttypmod" field a bit
better, but have not decided how to extend it to multiple fields.

devide it into two 16 bit integers ?

A mathematical package exists for infinite scale decimals, I think
it was part of a 56 bit RSA cracking effort. It has all thinkable
operations defined (some I have never heard of, and I am no beginner in math)
I think I wrote to the list about it in the past, but I can't find it anymore.

Andreas

On Mon, 11 May 1998, Thomas G. Lockhart wrote:

I think that PostgreSQL money type should be very useful if we could
remove the dollar sign. We can't use it with Lira/Peseta/Mark etc.
In europe now we have Euro. If we remove the $ it will be useful
otherwise we have to rename it to 'dollar'. ;-)

Have you tried compiling with "USE_LOCALE" turned on and with the right
setting for LC_xxx? The code is supposed to use local conventions, but I
don't know if it works in the way you want. I agree that it should...

PS: Is there a reason to left justify it ?

That is just an artifact of the column formatting; all columns are left
justified in psql afaik.

Seems there's some problems with type 'money'... I can't multiply or
divide 'money' types, and can't cast it properly to other data types.

prova=> select ename,job,hiredate, sal from employees;
ename |job | hiredate|sal
------+----------+----------+---------
ALLEN |SALESMAN |1981-02-20|$1,600.00
BLAKE |MANAGER |1981-05-01|$2,850.00
JONES |CLERK |1981-12-03|$950.00
MILLER|SALESMAN |1981-09-28|$1,250.00
CLARK |SALESMAN |1981-09-08|$1,500.00
KING |SALESMAN |1981-02-22|$1,250.00
(6 rows)

prova=> select ename,job,hiredate, sal*1.1 as dream from employees;
ERROR: There is no operator '*' for types 'money' and 'money'
You will either have to retype this query using an explicit cast,
or you will have to define the operator using CREATE OPERATOR

prova=> select ename,job,hiredate,sal, sal::float as dream from employees;
ename |job | hiredate|sal | dream
------+----------+----------+---------+----------
ALLEN |SALESMAN |1981-02-20|$1,600.00|1079143604
BLAKE |MANAGER |1981-05-01|$2,850.00|1079143508
JONES |CLERK |1981-12-03|$950.00 |1079143412
MILLER|SALESMAN |1981-09-28|$1,250.00|1079143316
CLARK |SALESMAN |1981-09-08|$1,500.00|1079143220
KING |SALESMAN |1981-02-22|$1,250.00|1079143120
(6 rows)

Is this a bug ?
Jose'

Thus spake Jose' Soares Da Silva

I think that PostgreSQL money type should be very useful if we could
remove the dollar sign. We can't use it with Lira/Peseta/Mark etc.
In europe now we have Euro. If we remove the $ it will be useful otherwise
we have to rename it to 'dollar'. ;-)

I have been trying to remove this from the code. For some reason I can't
compile the system (something about wrong number of args to gettimeofday
in backend/tcop/postgres.c) but in the meantime, have you tried the
USE_LOCALE define? That should at least switch it to your local money
indicator.

PS: Is there a reason to left justify it ?

Not that I can think of but I'm not sure where you change that.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

Thus spake Thomas G. Lockhart

PS: Is there a reason to left justify it ?

That is just an artifact of the column formatting; all columns are left
justified in psql afaik.

Ints are right formatted so it must be possible to do.

-- 
D'Arcy J.M. Cain <darcy@{druid|vex}.net>   |  Democracy is three wolves
http://www.druid.net/darcy/                |  and a sheep voting on
+1 416 424 2871     (DoD#0082)    (eNTP)   |  what's for dinner.

I've just finished working on the type
conversion algorithms so understand the current "atttypmod" field a
bit better, but have not decided how to extend it to multiple fields.

divide it into two 16 bit integers ?

At the moment it already _is_ a 16 bit integer, so it would have to be
divided into two 8 bit integers. Still OK, but then it must be a
positive number, so one field can be only 7 bits. I was thinking of
trying to solve the problem generally so that a type definition can also
define a "type support type" similar to the current atttypmod, but which
could be single or multiple numbers, or a string, or...

Don't know if it would be generally useful though; still thinking about
how to implement different character sets and collation sequences for
strings and it seems like this might help.

A mathematical package exists for infinite scale decimals, I think
it was part of a 56 bit RSA cracking effort. It has all thinkable
operations defined...

Well, if you find it again let us know ;) In the meantime, the 64-bit
integers are probably the best candidate implementation.

- Tom

Seems there's some problems with type 'money'... I can't multiply or
divide 'money' types, and can't cast it properly to other data types.
Is this a bug ?

With the new type conversion code:

tgl=> create table mm (m money);
CREATE
tgl=> insert into mm values ('$1600.00');
INSERT 268105 1
tgl=> select m * 1.1 from mm;
?column?
---------
$1,760.00
(1 row)

But,

tgl=> select cast(m as float8) from mm;
float8
----------
1077124288
(1 row)

So there is some funny interaction on the casting, the same as you found
in v6.3.2 (and presumably forever), which I will look into...

- Tom

prova=> select var as my_varchar from prova where var = '12';
my_varchar
----------
12 <--right justified
(1 row)

prova=> select var as my_varchar from prova;
my_varchar
----------
12 <--left justified, this time ???
a12
a12
(3 rows)
Jose'

I can't reproduce this here.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

The only other thing which would need to be handled is how to pass along
the two value precision/scale parameters which are a part of the
declaration for these types. I've just finished working on the type
conversion algorithms so understand the current "atttypmod" field a bit
better, but have not decided how to extend it to multiple fields.

I have thought about this. Just bitmask the 16-bits to two 8-bit
quantities. Give you max 256 length with 256 currencies.

The only place they are used is in the type-specific *.c function, so
you just us the mask there, or create a union of :8 and :8 and reference
it that way.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

I've just finished working on the type
conversion algorithms so understand the current "atttypmod" field a bit
better, but have not decided how to extend it to multiple fields.

devide it into two 16 bit integers ?

atttypmod is only 16 bits, so it would be two 8-bit values. I can
change it to 32-bits if needed.

A mathematical package exists for infinite scale decimals, I think
it was part of a 56 bit RSA cracking effort. It has all thinkable
operations defined (some I have never heard of, and I am no beginner in math)
I think I wrote to the list about it in the past, but I can't find it anymore.

Maybe Marc can find it.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

I've just finished working on the type
conversion algorithms so understand the current "atttypmod" field a
bit better, but have not decided how to extend it to multiple fields.

divide it into two 16 bit integers ?

At the moment it already _is_ a 16 bit integer, so it would have to be
divided into two 8 bit integers. Still OK, but then it must be a
positive number, so one field can be only 7 bits. I was thinking of
trying to solve the problem generally so that a type definition can also
define a "type support type" similar to the current atttypmod, but which
could be single or multiple numbers, or a string, or...

use unsigned short, that is 8 bits.

Don't know if it would be generally useful though; still thinking about
how to implement different character sets and collation sequences for
strings and it seems like this might help.

A mathematical package exists for infinite scale decimals, I think
it was part of a 56 bit RSA cracking effort. It has all thinkable
operations defined...

Well, if you find it again let us know ;) In the meantime, the 64-bit
integers are probably the best candidate implementation.

Yes, the 64-bit idea is good.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

On Tue, 12 May 1998, Bruce Momjian wrote:

prova=> select var as my_varchar from prova where var = '12';
my_varchar
----------
12 <--right justified
(1 row)

prova=> select var as my_varchar from prova;
my_varchar
----------
12 <--left justified, this time ???
a12
a12
(3 rows)
Jose'

I can't reproduce this here.

Seems that PostgreSQL justify data based on data not on data type.
My environment is:
PostgreSQL v6.3
Linux 2.0.33

also Daniel A. Gauthier <3in7ifi@cmich.edu>
reported the same problem.

here my script:

create table prova ( my_varchar varchar );
CREATE
insert into prova values ('12');
INSERT 528521 1
insert into prova values ('a12');
INSERT 528522 1
select * from prova where my_varchar = '12';
my_varchar
----------
12
(1 row)

select * from prova;
my_varchar
----------
12
a12
(2 rows)

EOF
Jose'

create table prova ( my_varchar varchar );
CREATE
insert into prova values ('12');
INSERT 528521 1
insert into prova values ('a12');
INSERT 528522 1
select * from prova where my_varchar = '12';
my_varchar
----------
12
(1 row)

select * from prova;
my_varchar
----------
12
a12
(2 rows)

OK, I can reproduce this now. I would love to know why it is happening.
Seems very strange to me.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Bruce Momjian <maillist@candle.pha.pa.us> writes:

I have thought about this. Just bitmask the 16-bits to two 8-bit
quantities. Give you max 256 length with 256 currencies.

Uh, no: what we were discussing was the total width and decimal place
position of exact numerics. Probably, 255 numeric digits are enough
for practical purposes, so I don't feel an urgent need to make atttypmod
wider for this. But if you want to make it 32 bits, that would
eliminate any concern --- we'd have room for 64k-digit numerics...

If we're going to associate currencies with the money datatype, the
currency needs to be part of the data, not part of the column type.
I need to be able to store amounts of different currencies in the same
column. (Otherwise, a transaction log would need a separate column for
every possible currency, all but one of which would be null in any given
row. Ick.)

regards, tom lane

Bruce Momjian <maillist@candle.pha.pa.us> writes:

I have thought about this. Just bitmask the 16-bits to two 8-bit
quantities. Give you max 256 length with 256 currencies.

Uh, no: what we were discussing was the total width and decimal place
position of exact numerics. Probably, 255 numeric digits are enough
for practical purposes, so I don't feel an urgent need to make atttypmod
wider for this. But if you want to make it 32 bits, that would
eliminate any concern --- we'd have room for 64k-digit numerics...

If we're going to associate currencies with the money datatype, the
currency needs to be part of the data, not part of the column type.
I need to be able to store amounts of different currencies in the same
column. (Otherwise, a transaction log would need a separate column for
every possible currency, all but one of which would be null in any given
row. Ick.)

Yep, good point.

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)