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]; 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
