Thread: money or dollar type
Hi all,
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'. ;-)
----
PS: Is there a reason to left justify it ?
select dollar from prova;
dollar
----------
$300.32
$302.21
$312.10
$12,312.10
$12,386.00
$12,312.00
Thanks, Jose'
> 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
"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
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'
"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
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'
"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
> 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
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.
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.
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'
> 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)
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)