Postgresql 中文操作指南

8.2. Monetary Types #

money 类型存储具有固定小数精度的货币金额；参见 Table 8.3 。小数精度由数据库的 lc_monetary 设置确定。表中所示的范围假设有两个小数位。输入以多种格式接受，包括整数和浮点字面值以及典型的货币格式，例如 '$1,000.00' 。输出通常采用后一种形式，但取决于区域设置。

The money type stores a currency amount with a fixed fractional precision; see Table 8.3. The fractional precision is determined by the database’s lc_monetary setting. The range shown in the table assumes there are two fractional digits. Input is accepted in a variety of formats, including integer and floating-point literals, as well as typical currency formatting, such as '$1,000.00'. Output is generally in the latter form but depends on the locale.

Table 8.3. Monetary Types

Name

Storage Size

Description

Range

money

8 bytes

currency amount

-92233720368547758.08 to +92233720368547758.07

由于此数据类型的输出是区域相关的，因此可能无法将 money 数据加载到具有不同 lc_monetary 设置的数据库中。为避免问题，在将转储还原到新数据库之前，确保 lc_monetary 具有与被转储数据库相同或等效的值。

Since the output of this data type is locale-sensitive, it might not work to load money data into a database that has a different setting of lc_monetary. To avoid problems, before restoring a dump into a new database make sure lc_monetary has the same or equivalent value as in the database that was dumped.

numeric、int 和 bigint 数据类型的值可以强制转换为 money。可以先强制转换为 numeric 来从 real 和 double precision 数据类型进行转换，例如：

Values of the numeric, int, and bigint data types can be cast to money. Conversion from the real and double precision data types can be done by casting to numeric first, for example:

SELECT '12.34'::float8::numeric::money;

但是，不推荐这样做。浮点数不应用于处理货币，因为可能会出现舍入误差。

However, this is not recommended. Floating point numbers should not be used to handle money due to the potential for rounding errors.

可以将 money 值强制转换为 numeric，且不丢失精度。转换为其他类型可能会丢失精度，并且还必须分两个阶段完成：

A money value can be cast to numeric without loss of precision. Conversion to other types could potentially lose precision, and must also be done in two stages:

SELECT '52093.89'::money::numeric::float8;

用一个整数值对一个 money 值进行除法时，将截断小数部分为零。要获得舍入结果，请除以浮点值，或在除法之前将 money 值强制转换为 numeric，并在之后强制转换回 money。（后者更可取，以避免精度丢失的风险。）当一个 money 值除以另一个 money 值时，结果为 double precision（即一个纯数字，而不是货币）；货币单位在除法中相互抵消。

Division of a money value by an integer value is performed with truncation of the fractional part towards zero. To get a rounded result, divide by a floating-point value, or cast the money value to numeric before dividing and back to money afterwards. (The latter is preferable to avoid risking precision loss.) When a money value is divided by another money value, the result is double precision (i.e., a pure number, not money); the currency units cancel each other out in the division.