使用 PostgreSQL 处理货币数据

摘要：在本文中，我们将学习如何在 PostgreSQL 中处理货币数据。

目录

货币数据类型

货币

PostgreSQL 实际上确实有一个money数据类型。但不建议使用它，因为它不处理一分钱的小数部分，并且货币与数据库区域设置相关联。虽然 money 类型不是存储货币的最佳实践，但我确实认为，当希望将查询输出格式化为货币值时，money 对于转换数据非常方便。

浮点型

在任何使用带小数的正数和负数的系统中，浮点数通常都很流行（因为名称 float 意味着数字在数字小数位上浮动）。浮点型（real/float4）和双浮点数据类型（float8）可以用来存取货币数据，但它们通常并不理想，因为它们基本上不精确。

例如，这是正确的：

select 0.0001::float4;

这也是正确的：

select 0.0001::float4 + 0.0001::float4;

但是，如果我们尝试进行额外的分数运算，这并不会得到真正的预期结果：

select 0.0001::float4 + 0.0001::float4 + 0.0001::float4;

整型

很多人用integer来存储货币数据。整数不支持任何类型的小数，因此 100/3 可能等于 33.3333，但在整数的数学运算中，它只会得到 33。如果您知道您的最小单位是什么（甚至低至一分钱的分数部分），并且可以在数据库中使用乘数，这可以用于存储货币数据。因此，处理整数分钱的乘数为 100，如果您想表示 0.237928372 BTC 之类的金额，则乘数为 1000000000。这个单位是整体存储的，这解决了 float 无法表示的值的问题。这种技术仍然存在重大限制，因为integer最大只能存储 2147483647 的数字，bigint最大只能存储 9223372036854775807。

Integer 具有显著的性能和存储效率。它只是一个 4 字节大小的列，如果您使用的是bigint，则是 8 字节。另外，请记住，将货币存储为整数，要以传统格式输出，供前端或 sql 报告表示一元、一分或十进制数字，需要除法或转换为不同的数据类型。

数值型

numeric/decimal被广泛认为是在 PostgreSQL 中存储货币数据的理想数据类型。您可以输入很多小数位（10,000+ 位数字！），并且可以定义精度。数值数据类型有两个限定符，即精度和小数位，用于定义要使用的合理小数位数。创建类型时，它看起来会像NUMERIC(7,5)这样，其中精度为 7，小数部分为 5。

• 精度是小数点前后的总位数。您需要将其设置为您可能需要存储的最高数量。所以这里 99.99999 是最大值，-99.99999 是最小值。
• 小数部分是小数点后的数字数，因此这将是 5 位小数。

选择小数部位意味着 PostgreSQL 将在某个时候对数字进行舍入。如果要防止四舍五入，请确保您的 scale 数字确实非常高。

与整数相比，数值数据类型要占用很多空间，每列行 10 个字节。因此，如果您非常关心占用空间和性能，而数值型的精度不是那么重要，那么使用 integer 可能会更好。

存储货币数据

好的，我们有一个数据类型来存储实际的美分、美元、欧元等。现在我们如何存储货币？一般来说，如果您需要同时以多种货币存储资金，最好将货币与数字本身一起存储。如果您想要官方货币代码，请参阅 ISO 4217。您可以使用自定义检查约束来要求输入数据仅为某些货币类型，比如，如果您使用的是可能看起来像美元、英镑和欧元的货币值。

这是本教程中加载的内容。

CREATE TABLE products (
    sku SERIAL PRIMARY KEY,
    name VARCHAR(255),
    price NUMERIC(7,5),
    currency TEXT CHECK (currency IN ('USD', 'EUR', 'GBP'))
);

如果您正在处理多种格式的货币，则需要考虑很多因素。在许多情况下，在事务中会发生很多事情。假设数据库中以 USD 为单位设置的价格，要以 GBP 显示给用户。您会有一个像上面一样的设置，还有另外一个 GBP 汇率表。也许该表会通过 API 进行更新，因为货币价值在一天中会波动。您可能以一种货币设置价格，而以另一种货币支付价格，输入的是购买时支付的金额。

货币处理函数

• 平均值

四舍五入到最接近的美分

SELECT ROUND(AVG(price), 2) AS truncated_average_price
FROM products;

• 用 ceiling 四舍五入

求和并四舍五入到最接近的整数

SELECT CEIL(SUM(price)) AS rounded_total_price
FROM products;

• 用 floor 向下舍入

求和并向下舍入到最接近的整数

SELECT FLOOR(SUM(price)) AS rounded_total_price
FROM products;

• 中位数

计算中位数可能会稍微复杂一些，因为 PostgreSQL 没有内置的中位数函数，但您可以使用窗口函数来计算它

WITH sorted_prices AS (
    SELECT price,
           ROW_NUMBER() OVER (ORDER BY price) as r,
           COUNT(*) OVER () as total_count
    FROM products
)
SELECT FLOOR(AVG(price)) AS rounded_median_price
FROM sorted_prices
WHERE r IN (total_count / 2, (total_count + 1) / 2);

• 转换到货币类型

如果您希望结果包含货币符号、逗号和点号。

SELECT CEIL(SUM(price))::money AS rounded_total_price_money
FROM products;

请注意，货币符号将根据您的区域设置进行显示，用show lc_monetary;会告诉您区域是什么设置，您可以将其更新为其他货币。

总结

• 如果您只要处理整数的美分，并且您不需要美分的小数部分，可以使用int或bigint。这样可以节省空间并提供更好的性能。将您的钱以美分存储，并在您的输出中转换为小数。如果所有货币都是同一类型，这也确实是首选的方法。如果您经常更换货币并处理小数美分，请尝试numeric。
• 要以分数美分为单位存储货币，可以使用numeric，甚至存储到许多许多小数点。如果您需要支持大量精度的货币值，这是最好的选择，但它会有一些存储和性能成本。
• 将货币与实际货币值分开存储，以便您可以对货币换算进行计算。