About the MONEY type
I think MONEY is a great datatype, at least in theory.
It's stored as a 64 bit binary integer with an implied, fixed decimal
scale. This means that storage is conserved and operations are as fast
and exact as possible (to the implied decimal scale.)
Unfortunately it has a couple of significant drawbacks.
1. You can't specify the scale on a column-by-column basis, such as
MONEY(2) and MONEY(4), which would be useful in many databases[1]for example, many databases store the price of a single item, such as a single nut and bolt, as a 1e-4 or 1e-6 fraction of the local currency, but store the totals of invoices to the law-mandated precision, say 1e-2.;
instead, the scale is a configuration setting (lc_monetary) that is
only used when converting MONEY values to/from their string
representation or other datatypes. Inside the DB it's just a bigint.
2. By default it outputs its values in the US locale format: $1,200.00
which looks kind of silly (or out of place) as a data representation
format between database and application layers.
Is there any value I can assign to lc_monetary, or any other
configuration variable, that will make MONEY display its values
without dollar signs and thousand separators? (like NUMERIC would.) I
understand I can just cast each MONEY column to ::numeric to get that
representation format, or alternatively strip the dollar and commas on
the application side, but it would be easier to just specify it as a
configuration variable. I hoped the value 'C' would give a
locale-agnostic format (as far as such a thing can exist) but it
defaults to US locale as well.
Finally, I would like to throw out there the idea of a MONEY(s) or
maybe FIXED(s) type, to represent numbers as 64 bit binary integers
with an implied decimal scale that is specific to each column (and no
dollars or commas on output.) Unfortunately I have no idea how big of
a change this would require in the codebase. It's probably a
completely new datatype. Maybe there is already such a type as an
extension, under a different name?
-Tobia
[1]: for example, many databases store the price of a single item, such as a single nut and bolt, as a 1e-4 or 1e-6 fraction of the local currency, but store the totals of invoices to the law-mandated precision, say 1e-2.
as a single nut and bolt, as a 1e-4 or 1e-6 fraction of the local
currency, but store the totals of invoices to the law-mandated
precision, say 1e-2.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Tobia Conforto schrieb am 30.11.2016 um 12:15:
I think MONEY is a great datatype, at least in theory.
I personally find it pretty useless to be honest - especially because the currency symbol depends on the client.
So if I store a money value in the database, some clients see CHF, some see Kč, some see £ and others might see € - all see the same amount.
Which seems totally wrong because 10€ is something completely different then 10Kč or 10£.
Plus: inside a programming language (e.g. Java/JDBC) it's hard to work with the values because the database sends the values as a string (it has to because of the currency symbol) but in reality it is a number - but you can't just convert the String to a number again because of the symbol.
So I always recommend to not use it (in Postgres just as well as in other DBMS, e.g. SQL Server)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 30/11/16 12:05, Thomas Kellerer wrote:
Tobia Conforto schrieb am 30.11.2016 um 12:15:
I think MONEY is a great datatype, at least in theory.
I personally find it pretty useless to be honest - especially because
the currency symbol depends on the client.So if I store a money value in the database, some clients see CHF,
some see Kč, some see £ and others might see € - all see the same
amount. Which seems totally wrong because 10€ is something completely
different then 10Kč or 10£.Plus: inside a programming language (e.g. Java/JDBC) it's hard to
work with the values because the database sends the values as a
string (it has to because of the currency symbol) but in reality it
is a number - but you can't just convert the String to a number again
because of the symbol.So I always recommend to not use it (in Postgres just as well as in
other DBMS, e.g. SQL Server)
I seem to remember that it was actually deprecated at some point - this
is going back quite a few years. This was later reversed, though I don't
know why.
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
I think MONEY is a great datatype, at least in theory.
I personally find it pretty useless to be honest - especially because
the currency symbol depends on the client.
I should have been more clear: I find the underlying idea of storing a
fixed-scale decimal number as a pre-scaled int8 column a good idea.
But MONEY is the only standard datatype that does so, and in its
current implementation it's horrible, because of all the shortcomings
we both mentioned.
That's why I was asking whether: 1. is there a way to remove the
dollar and commas from its default input/output format; and 2. is
there any other fixed-scale decimal extension type that is stored as
an int8, possibly with the scale specified as part of the column
definition (eg. FIXED(4))
-Tobia
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Nov 30, 2016 at 6:43 AM, Raymond O'Donnell <rod@iol.ie> wrote:
I seem to remember that it was actually deprecated at some point - this is
going back quite a few years. This was later reversed, though I don't know
why.
Because its pointless to deprecate something that you haven't replaced and
have no intention of just removing without a replacement.
I use money as a column type for a very specific reason:
I have data that comes to me in a money-like format (one which money can
handle, though I'm in the U.S. so its limitations don't affect me as much)
within a JSON body and I can use the "json_to_record"
to directly populate a target table without having either pre-process the
value or store it as text and then perform post-processing. Once I have it
in as money a simple cast to numeric is reliable.
Now, my GUI tool of choice chooses to simply return an empty string when
faced with data in money format but working within psql is solid.
David J.
On Wed, Nov 30, 2016 at 7:43 AM, Raymond O'Donnell <rod@iol.ie> wrote:
On 30/11/16 12:05, Thomas Kellerer wrote:
Tobia Conforto schrieb am 30.11.2016 um 12:15:
I think MONEY is a great datatype, at least in theory.
I personally find it pretty useless to be honest - especially because
the currency symbol depends on the client.So if I store a money value in the database, some clients see CHF,
some see Kč, some see £ and others might see € - all see the same
amount. Which seems totally wrong because 10€ is something completely
different then 10Kč or 10£.Plus: inside a programming language (e.g. Java/JDBC) it's hard to
work with the values because the database sends the values as a
string (it has to because of the currency symbol) but in reality it
is a number - but you can't just convert the String to a number again
because of the symbol.So I always recommend to not use it (in Postgres just as well as in
other DBMS, e.g. SQL Server)I seem to remember that it was actually deprecated at some point - this is
going back quite a few years. This was later reversed, though I don't know
why.
It was moved from a 32 bit implementation to a 64 bit one, and it was
given a lot of the previously missing basic infrastructure that was
needed to do normal simple things. I would personally have preferred
to get rid it for the problems you mentioned.
With respect to FIXED, the NUMERIC datatype is already fixed precision
so the name is poor. An all binary fixed precision datatype would be
a good idea for an extension, assuming one does not already exist (I
didn't check).
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
----- Original Message -----
From: "Merlin Moncure" <mmoncure@gmail.com>
To: "Raymond O'Donnell" <rod@iol.ie>
Cc: "Thomas Kellerer" <spam_eater@gmx.net>, "PostgreSQL General" <pgsql-general@postgresql.org>
Sent: Wednesday, November 30, 2016 11:41:39 AM
Subject: Re: [GENERAL] About the MONEY typeOn Wed, Nov 30, 2016 at 7:43 AM, Raymond O'Donnell <rod@iol.ie> wrote:
On 30/11/16 12:05, Thomas Kellerer wrote:
Tobia Conforto schrieb am 30.11.2016 um 12:15:
I think MONEY is a great datatype, at least in theory.
I personally find it pretty useless to be honest - especially because
the currency symbol depends on the client....
I seem to remember that it was actually deprecated at some point - this is
going back quite a few years. This was later reversed, though I don't know
why.It was moved from a 32 bit implementation to a 64 bit one, and it was
given a lot of the previously missing basic infrastructure ...
I recall a number of years ago reading about a money implementation that included different currency bases and exchange rate calculation. A quick Google search turned up
https://github.com/samv/pg-currency
which I am not sure is the same thing, but it looks like it might be something useful in the current context.
-- B
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Nov 30, 2016 at 1:23 PM, btober@computer.org <btober@broadstripe.net
wrote:
I recall a number of years ago reading about a money implementation that
included different currency bases and exchange rate calculation. A quick
Google search turned uphttps://github.com/samv/pg-currency
which I am not sure is the same thing, but it looks like it might be
something useful in the current context.-- B
Speaking generically, I guess maybe MONEY needs to be somewhat like a
TIMESTAMP. At least in PostgreSQL, a TIMESTAMP can contain a TIMEZONE. I
guess a MONEY type should contain a modifier identifying the issuer of the
currency (E.g. U.S. Dollar vs Canadian Dollar vs. Yen vs. Yuan vs.
"precious metal").
--
Heisenberg may have been here.
Unicode: http://xkcd.com/1726/
Maranatha! <><
John McKown
On Wed, Nov 30, 2016 at 2:16 PM, John McKown
<john.archie.mckown@gmail.com> wrote:
On Wed, Nov 30, 2016 at 1:23 PM, btober@computer.org
<btober@broadstripe.net> wrote:
Speaking generically, I guess maybe MONEY needs to be somewhat like a
TIMESTAMP. At least in PostgreSQL, a TIMESTAMP can contain a TIMEZONE. I
guess a MONEY type should contain a modifier identifying the issuer of the
currency (E.g. U.S. Dollar vs Canadian Dollar vs. Yen vs. Yuan vs. "precious
metal").
ISTM we already have that functionality; composite types. Had the
money type been written after we got composite types it might have
been done differently (or perhaps not at all). A similar observation
can be made against the geometric types.
Proper currency conversion of course is a complex topic; it'd be an
interesting thought experiment to imagine that functionality inside of
a type implementation.
The problem with the money type is that it simultaneously somehow does
too much and not enough. It kind of lives in twilight as a sneaky
fixed point integer implemented in binary. It's a scar from the heady
days of youth used to impress people :-).
merlin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 11/30/2016 12:16 PM, John McKown wrote:
Speaking generically, I guess maybe MONEY needs to be somewhat like a
TIMESTAMP. At least in PostgreSQL, a TIMESTAMP can contain a
TIMEZONE. I guess a MONEY type should contain a modifier identifying
the issuer of the currency (E.g. U.S. Dollar vs Canadian Dollar vs.
Yen vs. Yuan vs. "precious metal").
and then it would need to be able to convert between all those
units? great fun. it probably needs a time too, as those
conversion units vary with time. worse, they vary with where you
convert the money and which way, and how much the converter skims....
In the real world, US$ -> € -> US$ will not give you back the same amount.
note, btw, TIMESTAMP WITH TIME ZONE doesn't actually store the
timezone... rather, it converts it to an internal representation of GMT,
and then converts it back to display time at the client's current (or
specified) time zone.
--
john r pierce, recycling bits in santa cruz
My two cents . . .
On Wed, 2016-11-30 at 13:35 -0800, John R Pierce wrote:
On 11/30/2016 12:16 PM, John McKown wrote:
Speaking generically, I guess maybe MONEY needs to be somewhat
like a TIMESTAMP. At least in PostgreSQL, a TIMESTAMP can contain
a TIMEZONE. I guess a MONEY type should contain a modifier
identifying the issuer of the currency (E.g. U.S. Dollar vs
Canadian Dollar vs. Yen vs. Yuan vs. "precious metal").
and then it would need to be able to convert between all those
units? great fun. it probably needs a time too, as those
conversion units vary with time. worse, they vary with where you
convert the money and which way, and how much the converter
skims.... In the real world, US$ -> € -> US$ will not give you
back the same amount.
I don't believe the OP is talking about currency conversions using
exchange rates.
It sounds like he would like a printf style string held in the same
column so that a select of that column would return a string formatted
by the printf style arguments, and presumably any arithmetic operations
would return the correct result. Complicated.
Currently, working in multi-currency environments you need to have
three columns -- one defined as NUMERIC(15,3) another to hold the ISO
currency code and the date. The date is necessary due to countries
shifting the decimal place leftwards due to inflation. E.g., inflation
in Venuzuela is around 1,500%pa at the moment. Ergo, the paper money
becomes worthless and if you are still using computers with 32 bit
integers you end up doing addition by hand.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Wed, Nov 30, 2016 at 01:35:12PM -0800, John R Pierce wrote:
note, btw, TIMESTAMP WITH TIME ZONE doesn't actually store the timezone...
rather, it converts it to an internal representation of GMT, and then converts
it back to display time at the client's current (or specified) time zone.
Right, TIMESTAMP WITH TIME ZONE converts the timestamp value to the
local time zone on output. Imagine a monetary type that converted the
money amount to local currency on output --- that would be cool.
--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ As you are, so once was I. As I am, so you will be. +
+ Ancient Roman grave inscription +--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 18/12/16 12:25, Bruce Momjian wrote:
On Wed, Nov 30, 2016 at 01:35:12PM -0800, John R Pierce wrote:
note, btw, TIMESTAMP WITH TIME ZONE doesn't actually store the timezone...
rather, it converts it to an internal representation of GMT, and then converts
it back to display time at the client's current (or specified) time zone.Right, TIMESTAMP WITH TIME ZONE converts the timestamp value to the
local time zone on output. Imagine a monetary type that converted the
money amount to local currency on output --- that would be cool.
Hmm...
Would need to know the appropriate conversion rate. the 2 obvious
dates/times, on entry and now, may neither be the one wanted.
Also, often the buy/sell conversion rates are not the same!
Am sure there also other issues.
I don't think automatic conversion is as easy as you make it out to be.
Cheers,
Gavin
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Dec 18, 2016, at 5:23 PM, Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:
On 18/12/16 12:25, Bruce Momjian wrote:
On Wed, Nov 30, 2016 at 01:35:12PM -0800, John R Pierce wrote:
note, btw, TIMESTAMP WITH TIME ZONE doesn't actually store the timezone...
rather, it converts it to an internal representation of GMT, and then converts
it back to display time at the client's current (or specified) time zone.Right, TIMESTAMP WITH TIME ZONE converts the timestamp value to the
local time zone on output. Imagine a monetary type that converted the
money amount to local currency on output --- that would be cool.Hmm...
Would need to know the appropriate conversion rate. the 2 obvious dates/times, on entry and now, may neither be the one wanted.
Also, often the buy/sell conversion rates are not the same!
Am sure there also other issues.
I don't think automatic conversion is as easy as you make it out to be.
Cheers,
Gavin
Bets on how serious Mr. Pierce was are currently trending in Vegas.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 19/12/16 14:17, Rob Sargent wrote:
On Dec 18, 2016, at 5:23 PM, Gavin Flower <GavinFlower@archidevsys.co.nz> wrote:
On 18/12/16 12:25, Bruce Momjian wrote:
On Wed, Nov 30, 2016 at 01:35:12PM -0800, John R Pierce wrote:
note, btw, TIMESTAMP WITH TIME ZONE doesn't actually store the timezone...
rather, it converts it to an internal representation of GMT, and then converts
it back to display time at the client's current (or specified) time zone.Right, TIMESTAMP WITH TIME ZONE converts the timestamp value to the
local time zone on output. Imagine a monetary type that converted the
money amount to local currency on output --- that would be cool.Hmm...
Would need to know the appropriate conversion rate. the 2 obvious dates/times, on entry and now, may neither be the one wanted.
Also, often the buy/sell conversion rates are not the same!
Am sure there also other issues.
I don't think automatic conversion is as easy as you make it out to be.
Cheers,
Gavin
Bets on how serious Mr. Pierce was are currently trending in Vegas.
:-)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general