SQL PostgreSQL：货币应使用哪种数据类型？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15726535/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PostgreSQL: Which Datatype should be used for Currency?
提问by daydreamer
采纳答案by Chris Farmiloe
Numericwith forced 2 units precision. Never use float or float like datatype to represent currency because if you do, people are going to be unhappy when the financial report's bottom line figure is incorrect by + or - a few dollars.
具有强制 2 个单位精度的数字。永远不要使用 float 或 float 之类的数据类型来表示货币，因为如果这样做，当财务报告的底线数字不正确+或-几美元时，人们会不高兴。
The money type is just left in for historical reasons as far as I can tell.
据我所知，货币类型只是出于历史原因而保留的。
回答by Erwin Brandstetter
Your source is in no way official. It dates to 2011 and I don't even recognize the authors. If the money type was "discouraged" PostgreSQL would say so in the manual - which it doesn't.
你的消息来源绝不是官方的。它可以追溯到 2011 年，我什至不认识作者。如果货币类型是“不鼓励的”，PostgreSQL 会在手册中这么说——它没有。
For a more official source, read this thread in pgsql-general (from just this week!), with statements from core developers including D'Arcy J.M. Cain (original author of the money type) and Tom Lane:
有关更官方的来源，请阅读pgsql-general 中的此线程（仅从本周开始！），其中包含来自 D'Arcy JM Cain（货币类型的原始作者）和 Tom Lane 等核心开发人员的声明：
Basically, moneyhas its (limited) uses. The advantage over numericis performance.
基本上，money有它的（有限的）用途。优势numeric在于性能。
decimalis just an alias for numericin Postgres.
decimal只是numericPostgres 中的别名。
Related answer (and comments!) about improvements in recent releases:
关于最近版本改进的相关答案（和评论！）：
Personally, I like to store currency as integerrepresenting Cents. That's more efficient than any other of the mentioned options.
就个人而言，我喜欢将货币存储为integer代表美分。这比任何其他提到的选项更有效。
回答by Bohemian
Your choices are:
您的选择是：
integer: store the amount in cents. This is what EFTPOS transactions use.decimal(12,2): store the amount with exactly two decimal places. This what most general ledger software uses.float: terrible idea - inadequate accuracy. This is what naive developers use.
integer: 以美分存储金额。这就是 EFTPOS 交易使用的。decimal(12,2): 存储精确到小数点后两位的金额。这是大多数总账软件使用的。float: 糟糕的想法 - 准确性不足。这是天真的开发人员使用的。
Option 2 is the most common and easiest to work with. Make the precision (12 in my example, meaning 12 digits in all) as large or small as works best for you.
选项 2 是最常见和最容易使用的。使精度（在我的示例中为 12，意味着总共 12 位）尽可能大或小，以最适合您。
Note that if you are aggregating multiple transactions that were the result of a calculation (eg involving an exchange rate) into a single value that has business meaning, the precision should be higher to provide a accurate macro value; consider using something like decimal(18, 8)so the sum is accurate and the individual values can be rounded to cent precision for display.
请注意，如果您将计算结果（例如涉及汇率）的多个交易汇总为具有业务意义的单个值，则精度应更高以提供准确的宏观值；考虑使用类似的东西，decimal(18, 8)这样总和是准确的，并且可以将各个值四舍五入到分精度以进行显示。
回答by Michael Collette
I keep all of my monetary fields as:
我将所有货币字段保持为：
numeric(15,6)
numeric(15,6)
It seems excessive to have that many decimal places, but if there's even the slightest chance you will have to deal with multiple currencies you'll need that much precision for converting. No matter what I'm presenting a user, I always store to US Dollar. In that way I can readily convert to any other currency, given the conversion rate for the day involved.
拥有这么多小数位似乎有些过分，但如果您需要处理多种货币的可能性很小，那么您将需要如此高的转换精度。无论我向用户展示什么，我总是以美元存储。考虑到当天的兑换率，这样我就可以很容易地兑换成任何其他货币。
If you never do anything but one currency, the worst thing here is that you wasted a bit of space to store some zeroes.
如果你只做一种货币，那么最糟糕的事情就是你浪费了一点空间来存储一些零。
回答by Mani Gandham
Use a 64-bit integer stored as bigint
使用存储为的 64 位整数 bigint
I recommend using micro-dollars (or similar major currency). Micro means 1 millionth so 1 micro-dollar = $0.000001.
我建议使用小额美元（或类似的主要货币）。微型意味着百万分之一，所以 1 微美元 = 0.000001 美元。
- Simple to use and compatible with every language.
- Enough precision to handle fractions of a cent.
- Works for very small per-unit pricing (like ad impressions or API charges).
- Smaller data size for storage than strings or numerics.
- Easy to maintain accuracy through calculations and apply rounding at the final output.
- 使用简单且与每种语言兼容。
- 足够的精度来处理几分之一。
- 适用于非常小的单位定价（如广告展示次数或 API 费用）。
- 比字符串或数字更小的存储数据大小。
- 通过计算轻松保持准确性，并在最终输出中应用四舍五入。
回答by Max Hodges
Use BigIntto store currency as a positive integer representing how much to charge in the smallest currency unit (e.g., 100 cents to store $1.00 or 100 to store ￥100, a zero-decimal currency). This is what Stripe does--one the most important financial service companies for global ecommerce.
使用BigInt存储货币为代表多少的最小货币单位收取一定的正整数（如100美分存储$ 1.00 100存储¥100，零小数的货币）。这就是 Stripe 所做的——全球电子商务最重要的金融服务公司之一。
