Thread: function to format floats as money?
Hello, I'm curious to know if there is a function available in Postgres 7.0.3 (or 7.1) that will format a float style number as "money". I understand that the money type is going away in the future, so using a float type that is sometimes formatted like money seems like a good alternative. So ideally, I'm looking for a solution that won't go away when the money type does. :) Thanks! -mark http://mark.stosberg.com/
Mark Stosberg writes: > I'm curious to know if there is a function available in Postgres 7.0.3 > (or 7.1) that will format a float style number as "money". I understand > that the money type is going away in the future, so using a float type > that is sometimes formatted like money seems like a good alternative. So > ideally, I'm looking for a solution that won't go away when the money type > does. :) Thanks! to_char() for formatting. numeric for storage. Using floats for monetary amounts is not only an extremely bad idea because of the inexactness of storage and arithmetic, it might even be illegal if you're using it for official purposes. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
Peter Eisentraut wrote: > > Mark Stosberg writes: > > > I'm curious to know if there is a function available in Postgres 7.0.3 > > (or 7.1) that will format a float style number as "money". I understand > > that the money type is going away in the future, so using a float type > > that is sometimes formatted like money seems like a good alternative. So > > ideally, I'm looking for a solution that won't go away when the money type > > does. :) Thanks! > > to_char() for formatting. > > numeric for storage. > > Using floats for monetary amounts is not only an extremely bad idea > because of the inexactness of storage and arithmetic, it might even be > illegal if you're using it for official purposes. Thanks Peter. So if the money type is going away, and floats can be illegal, whats the best way to store money? -mark personal website } Summersault Website Development http://mark.stosberg.com/ { http://www.summersault.com/
On Tue, Apr 17, 2001 at 10:31:49AM -0500, Mark Stosberg wrote: > Peter Eisentraut wrote: > > > > Mark Stosberg writes: > > > > > I'm curious to know if there is a function available in Postgres 7.0.3 > > > (or 7.1) that will format a float style number as "money". I understand > > > that the money type is going away in the future, so using a float type > > > that is sometimes formatted like money seems like a good alternative. So > > > ideally, I'm looking for a solution that won't go away when the money type > > > does. :) Thanks! > > > > to_char() for formatting. > > > > numeric for storage. > > > > Using floats for monetary amounts is not only an extremely bad idea > > because of the inexactness of storage and arithmetic, it might even be > > illegal if you're using it for official purposes. > > Thanks Peter. > > So if the money type is going away, and floats can be illegal, whats > the best way to store money? Why, in the bank, or under your mattress, depending on your level of paranoia, of course. But seriously, numeric(10,2) (or whatever precision and scale is correct for your application) is the standard answer. Ross
Ross writes: > But seriously, numeric(10,2) (or whatever precision and scale is correct > for your application) is the standard answer. Floats are fine for money as long as you only add and subtract and don't deal in amounts that won't fit in the mantissa. -- John Hasler john@dhh.gt.org (John Hasler) Dancing Horse Hill Elmwood, WI
On Tue, Apr 17, 2001 at 01:52:16PM -0500, John Hasler wrote: > Ross writes: > > But seriously, numeric(10,2) (or whatever precision and scale is correct > > for your application) is the standard answer. > > Floats are fine for money as long as you only add and subtract and don't > deal in amounts that won't fit in the mantissa. Or you're writing software in Germany (all of the EU now?) that _might_ get used in an offical capacity. Ross
I wrote: > Floats are fine for money as long as you only add and subtract and don't > deal in amounts that won't fit in the mantissa. Ross writes: > Or you're writing software in Germany (all of the EU now?) that _might_ get > used in an offical capacity. I was referring to what actually works, not to what might or might not meet with the approval of some officialdom or other. The two seldom bear any discernible relationship. -- John Hasler john@dhh.gt.org (John Hasler) Dancing Horse Hill Elmwood, WI
Now that I've figured out that numeric is good for storing money, and that I can format with like this: to_char(price, '9,999,999.99') as price Then I discovered that sometimes this returns leading spaces I don't want. I can get rid of them like this: trim(to_char(price, '9,999,999.99')) as price Is that the recommended money formatting style, for amounts less than 9,999,999.99? (assuming I'll tack on my own currency symbol? ). Other there other general styles that folks like for this? Thanks, -mark personal website } Summersault Website Development http://mark.stosberg.com/ { http://www.summersault.com/
On Thu, Apr 19, 2001 at 02:53:38PM -0500, Mark Stosberg wrote:
>
> Now that I've figured out that numeric is good for storing money, and
> that I can format with like this:
>
> to_char(price, '9,999,999.99') as price
>
> Then I discovered that sometimes this returns leading spaces I don't
> want. I can get rid of them like this:
>
> trim(to_char(price, '9,999,999.99')) as price
>
> Is that the recommended money formatting style, for amounts less than
> 9,999,999.99? (assuming I'll tack on my own currency symbol? ). Other
> there other general styles that folks like for this? Thanks,
May be try docs, what? :-)
test=# select to_char(123456, '9,999,999.99'); to_char
--------------- 123,456.00
(1 row)
test=# select to_char(123456, 'FM9,999,999.99');to_char
---------123,456
(1 row)
test=# select to_char(123456, 'FM9,999,999.00'); to_char
------------123,456.00
(1 row)
test=# select to_char(123, 'FM0,999,999.00'); to_char
--------------0,000,123.00
(1 row)
test=# select to_char(123456, 'LFM9,999,999.00'); to_char
------------
$123,456.00
(1 row)
FM ....fill mode, skip blank spaces and zeroes (if not set '0' instead '9')
L ....currency symbol (from actual locales)
Right?
Karel
-- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/C, PostgreSQL, PHP, WWW, http://docs.linux.cz,
http://mape.jcu.cz