使用 Java 在 PostgreSQL 上的货币数据

Money data on PostgreSQL using Java

提问by Arya

I'm writing a Java program that mines currency exchange data. The data can have multiple digits in the decimal such as "0.973047". After some research I was able to find out BigDecimal is the right data type for Java, but which data type should I be using for PostgreSQL?

我正在编写一个挖掘货币兑换数据的 Java 程序。数据可以有多个十进制数字，例如“0.973047”。经过一番研究，我发现 BigDecimal 是适合 Java 的数据类型，但是我应该为 PostgreSQL 使用哪种数据类型？

采纳答案by Basil Bourque

NUMERIC/DECIMAL

NUMERIC/DECIMAL

As Joachim Isaksson said, you want to use NUMERIC/DECIMALtype, as an arbitrary precision type.

正如Joachim Isaksson 所说，您希望使用NUMERIC/DECIMAL类型作为任意精度类型。

Two important points about NUMERIC/DECIMAL:

关于NUMERIC/ 的两个要点DECIMAL：

• Read the doccarefully to learn that you should specify the scale to avoid the default scale of 0, meaning integer values where the decimal fraction gets lopped off. While this is one of the places where Postgres strays from standard SQL (giving you any scale up to the implementation limit). So failing to specify the scale is a poor choice.
• The SQL types NUMERIC& DECIMALare close but not identicalaccording to the SQL Standard. In SQL:92, your specified precision for NUMERICis respected, whereas for DECIMALthe database server is allowed to add additional precision beyond what you specified. Here again Postgres strays a bit from the standard, with both NUMERIC& DECIMALdocumentedas equivalent.

• 仔细阅读文档以了解您应该指定比例以避免默认比例 0，这意味着小数部分被删除的整数值。虽然这是 Postgres 偏离标准 SQL 的地方之一（让您可以扩展到实现限制）。所以不指定比例是一个糟糕的选择。
• 在SQL类型NUMERIC和DECIMAL是接近，但不完全相同根据SQL标准。在SQL:92 中，您指定的精度NUMERIC是被尊重的，而DECIMAL数据库服务器允许添加超出您指定的精度。这里 Postgres 再次偏离标准，两者NUMERIC&DECIMAL记录为等效。

Terms:

条款：

• Precision is total number of digits in a number.
• Scale is the number of digits to the right of the decimal point (the decimal fraction).
• ( Precision - Scale ) = Number of digits to the left of decimal point (integer portion).

• 精度是数字中的总位数。
• 小数位数是小数点右边的位数（小数部分）。
• ( Precision - Scale ) = 小数点左边的位数（整数部分）。

Be clear on your project's specs for precision and scale:

明确项目的精度和规模规格：

• Big
  The precision must be big enough to handle larger numbers that might be needed in the future. Meaning… Perhaps your app today works in amounts of thousands of USD but in the future must perform roll-up reports that end up in the millions.
• Small
  For some accounting purposes, you may need to store a fraction of a the smallest currency amount. Meaning… More than 3 or 4 decimal places rather than the 2 needed for a penny in USD.

• 大
  精度必须足够大以处理将来可能需要的更大的数字。意思是……也许您的应用程序今天以数千美元的价格运行，但将来必须执行最终达到数百万美元的汇总报告。
• 小
  出于某些会计目的，您可能需要存储最小货币金额的一小部分。意思是……多于 3 或 4 个小数位，而不是一分钱所需的 2 位美元。

Avoid MONEYtype

避免MONEY类型

Postgres offers a MONEYtype as well. That may sound right, but probably not best for most purposes. One downside is that with MONEYthe scale is set by a database-wide configuration settingbased on locale. So that setting can vary dangerously easily when you switch servers or make other changes. Furthermore, you cannot control that setting for specific columns, while you can set the scale on each column of NUMERICtype. Lastly, MONEYis not standard SQL as shown in this list of standard SQL data types. Postgres includes MONEYfor the convenience of folks porting data from other database systems.

Postgres 也提供了一种MONEY类型。这听起来可能是对的，但对于大多数用途来说可能不是最好的。一个缺点是，与MONEY该刻度由数据库范围内的配置设置中设置基于语言环境。因此，当您切换服务器或进行其他更改时，该设置很容易发生危险的变化。此外，您无法控制特定列的该设置，而您可以在每个NUMERIC类型的列上设置比例。最后，MONEY不是标准 SQL 数据类型列表中所示的标准 SQL。Postgres 包括MONEY为了方便人们从其他数据库系统移植数据。

Move the Decimal Point

移动小数点

Another alternative employed by some is moving the decimal point, and just store in large integer data type.

一些人采用的另一种选择是移动小数点，并只存储在大整数数据类型中。

For example, If storing USDdollars to the penny, multiple any given fractional number by 100, cast to an integer type, and proceed. For example, $123.45 becomes the integer 12,345.

例如，如果将美元存储为一分钱，请将任何给定的小数乘以 100，转换为整数类型，然后继续。例如，$123.45 变为整数 12,345。

The benefit to this approach is faster execution times. Operations such as sumare very fast when performed on integers. Another benefit to integers is less memory usage.

这种方法的好处是更快的执行时间。sum对整数执行之类的操作非常快。整数的另一个好处是更少的内存使用。

I find this approach annoying, confusing, and risky. Annoying because computers should be working forus, not against us. Risky because some programmer or user may neglect to multiply/divide to convert back to fractional number, giving incorrect results. If working in a system without good support for accurate fractional numbers, this approach might be an acceptable workaround.

我发现这种方法很烦人、令人困惑且有风险。烦人，因为计算机应该为我们工作，而不是对我们不利。有风险，因为某些程序员或用户可能会忽略乘法/除法以转换回小数，从而给出不正确的结果。如果在一个对精确小数没有很好支持的系统中工作，这种方法可能是一个可以接受的解决方法。

I don't see any advantage to moving the decimal point when we have DECIMAL/NUMERICin SQL and BigDecimalin Java.

当我们在 SQL 和Java 中使用DECIMAL/时，我认为移动小数点没有任何好处。NUMERICBigDecimal

Rounding & NaN

四舍五入 & NaN

In your app's programming, as well as any calculations made on the Postgres server-side, be very careful and aware of rounding and truncationin the decimal fraction. And test for inadvertent NaNspopping up.

在您的应用程序编程中，以及在 Postgres 服务器端进行的任何计算中，都要非常小心并注意小数部分的舍入和截断。并测试无意中弹出的NaN。

In both sides, app and Postgres, always avoid floating pointdata types for money work. Floating point is designed for performance speed, but at the cost of accuracy.Calculations may result in seemingly crazy extra digits in the decimal fraction. Not good for financial/money or other purposes where accuracy matters.

在 app 和 Postgres 双方，始终避免使用浮点数据类型进行金钱工作。浮点是为性能速度而设计的，但以牺牲准确性为代价。计算可能会导致小数部分中看似疯狂的额外数字。不利于财务/金钱或准确性很重要的其他目的。

BigDecimal

BigDecimal

Yes, in Java, you want BigDecimalas your arbitrary precision type. BigDecimalis slower and uses more memory, but will accurately store your money amounts. SQL NUMERIC/DECIMALshould map to BigDecimalas discussed hereand on StackOverflow.

是的，在 Java 中，您希望将其BigDecimal作为任意精度类型。BigDecimal速度较慢并使用更多内存，但会准确存储您的金额。SQL NUMERIC/DECIMAL应该映射到这里和StackOverflow上的BigDecimal讨论。

BigDecimalis one of the best things about Java. I don't know of any other platform with a similar class, especially one so well-implemented and well-honed with major enhancements and fixes made over the years.

BigDecimal是关于 Java 的最好的事情之一。我不知道有任何其他平台具有类似的类，尤其是一个实施良好且经过多年重大改进和修复的平台。

Using BigDecimalis definitely slower than using Java's floating-point types, float& double. But in real-world apps I doubt your money calculations are going to be any bottleneck. And besides, which do you or your customers want: the fastestmoney calculations, or accuratemoney calculations?

使用BigDecimal肯定比使用Java 的浮点类型, float&慢double。但在现实世界的应用程序中，我怀疑您的资金计算将成为任何瓶颈。此外，您或您的客户想要哪种：最快的货币计算，或准确的货币计算？

I have always thought of BigDecimalas the biggest sleeper feature in Java, the most important advantage to using the Java platform over so many other platforms lacking such sophisticated support for fractional numbers.

我一直认为BigDecimalJava 最大的睡眠特性是使用 Java 平台的最重要优势，而不是许多其他缺乏对小数的复杂支持的平台。

Similar question: Best Data Type For Currency

类似问题：Best Data Type For Currency

回答by Joachim Isaksson

To get as good (and exact) precision as possible, you can use NUMERIC(or its alias DECIMAL), which has high precision and allows you to decide the precision you require;

为了获得尽可能好的（和精确的）精度，您可以使用NUMERIC（或其别名DECIMAL），它具有高精度并允许您决定所需的精度；

NUMERIC

User-specified precision, exact up to 131072 digits before the decimal point; up to 16383 digits after the decimal point

数字

用户指定精度，精确到小数点前131072位；小数点后最多 16383 位

回答by chrylis -cautiouslyoptimistic-

Generally, money shouldn't be stored as floating-point. The best approach is usually to store the amount of money as an integer of the smallest allowable size (for example, one U.S. cent) and format it for input and display. This is essentially what a fixed-precision DECIMALcolumn does in SQL, but if you transfer it back into Java, you still run the risk of losing precision (e.g., what happens if you split exactly half of the last allowable digit)?

通常，货币不应存储为浮点数。最好的方法通常是将金额存储为最小允许大小的整数（例如，一美分），并将其格式化以进行输入和显示。这本质上是DECIMALSQL 中固定精度列的作用，但是如果您将其传输回 Java，您仍然会面临失去精度的风险（例如，如果您将最后一个允许的数字恰好拆分为一半会发生什么）？