原文地址：https://www.migops.com/blog/handling-trailing-zeros-with-numeric-datatype-in-postgresql/
表演时Oracle 到 Postgres 的迁移，我们可能会遇到许多有趣的转换挑战。有时，相同的逻辑可能会在两者之间产生不同的行为Oracle 和 Postgres. 此类转换中的小错误可能会在修复应用程序功能时产生大量工作。其中一个错误是在 PostgreSQL 中处理数字数据类型的尾随零时，在将Oracle 中的 NUMBER 数据类型转换为 PostgreSQL 中的NUMERIC 数据类型期间。在这篇博文中，我们将讨论在 Postgres 中使用数字数据类型处理尾随零的方法。
精度和规模
精度是数字的位数，包括小数点后的位数。而刻度是小数点后的位数。对于 123.45，精度为 5，比例为 2。
Oracle 中的 NUMBER 数据类型
Oracle 中的 Number 数据类型可以存储具有精度和小数位数的正整数和负整数。在 Oracle 中，Precision 的范围可以在 0 到 38 位之间，而比例范围在 -84 到 127 位之间。Oracle 也允许负比例，但我们将在下一篇文章中详细讨论。
在以下示例中，精度为 20，小数位数为 10。
NUMBER (20,10) -> Precision is 20 and Scale is 10复制
PostgreSQL 中的 NUMERIC 数据类型
PostgreSQL 中的 Numeric 数据类型可以存储具有一定精度和比例的正整数和负整数。精度范围可以从 0 到 131072 位，而刻度范围可以从 0 到 16383 位。
Oracle 中数字数据类型的行为
让我们考虑一个 Oracle 中具有 NUMBER 数据类型的表的示例。
CREATE TABLE test_number(i NUMBER(20,10));复制
我们现在将使用以下命令插入一些数据。
INSERT INTO test_number VALUES(10.0500);复制
现在，让我们尝试选择这些数据，看看是否有任何尾随零。正如我们在输出中看到的，我们没有看到尾随零。
SQL> SELECT * FROM test_number; I ---------- 10.05复制
PostgreSQL 中数值数据类型的行为
让我们考虑一个 PostgreSQL 中具有 NUMERIC 数据类型的表的示例。
CREATE TABLE test_numeric(i NUMERIC(20,10));复制
我们现在将使用以下命令插入在 Oracle 中插入的相同数据。
INSERT INTO test_numeric VALUES(10.0500);复制
现在，让我们尝试选择这些数据，看看是否有任何尾随零。
postgres=# SELECT * FROM test_numeric; i --------------- 10.0500000000 (1 row)复制
在上面的输出中，我们可以注意到有尾随零直到指定给列的比例的限制。在此示例中，第 i 列的数据类型为 NUMERIC(20,10)，因此比例为 10。因此，小数点后有 10 位数字。
在 PostgreSQL 中修复尾随零的解决方法
根据我们部署的 PostgreSQL 版本，有多种解决方案可以避免尾随零。
直到 PostgreSQL 12
在 PostgreSQL 12 之前，我们没有任何可以帮助进行简单转换的直接函数。出于这个原因，我们必须利用一种不同的方法将列类型转换为文本数据类型，然后使用修剪函数。
请参阅以下示例，我们可以在其中使用trim() 函数。
postgres=# SELECT trim(trailing '0' from 10.00500::text)::numeric; rtrim -------- 10.005 (1 row)复制
PostgreSQL 13 及以上
从 PostgreSQL 13 开始，我们可以简单地利用 trim_scale() 函数来解决尾随零的问题。
让我们看看下面的示例，其中我们使用了 trim_scale()来修剪尾随零，而不使用任何类型转换。
postgres=# SELECT trim_scale(10.00500); trim_scale ------------ 10.005 (1 row)复制
但是，上述解决方法需要对应用程序进行一些更改。嵌入式 SQL 代码或由 ORM 生成的 SQL 必须通过应用程序中的一些更改来处理这些数据类型。
有没有办法让它无缝应用？让我们在下一节讨论这个问题。
在不更改应用程序的情况下修复尾随的解决方案
为了避免任何应用程序级别的更改来处理尾随零，我们可以在 Postgres 中使用 Numeric 数据类型，而无需任何精度或比例。考虑以下示例，其中我们之前创建了同一个表，但没有定义任何精度或比例。
CREATE TABLE test_numeric(i numeric); INSERT INTO test_numeric VALUES(10.0500); postgres=# SELECT * FROM test_number; i --------- 10.0500 (1 row)复制
在上面的输出中，我们可以注意到输出显示了应用程序插入的确切数量的零，没有额外的零。我们还看到，当 Oracle 修剪零时，输出也没有修剪，如第一个示例所示。在 Oracle 中，如果用户插入 0.0500，则在选择时会将其修剪为 0.05。如果插入这些额外的零是有特定原因的，那么对于应用程序来说，不修剪这些零是非常重要的。但是，如果需要像 Oracle 那样修剪零，那么我们可以利用前面讨论的解决方法。
使用没有精度和小数位数的数值数据类型的含义
这是一个重要的问题，即仅使用纯数字数据类型的含义以及它的精度或比例是多少。虽然没有太多混淆，但它只是保持在精度和规模的默认限制内。这意味着该值最多可以是 131072 位（精度），或者比例可以在 0 到 16383 位之间，但对比例或精度没有限制。如果应用程序知道对规模或精度没有保证限制，那么使用此解决方案就没有问题。
结论
到目前为止，我们已经了解了如何在 PostgreSQL 中处理带有数字数据类型的尾随零。在我们的下一篇博客文章中，我们将讨论在 Oracle 中将负比例的 NUMBER 数据类型转换为 PostgreSQL。将来，我们将讨论 PostgreSQL 的数值数据类型在性能方面的影响。请订阅我们的时事通讯获取有关我们关于 PostgreSQL 和迁移的文章和最新更新的通知。如果您正在寻找有关 PostgreSQL 迁移的任何服务或 PostgreSQL 咨询服务，请填写以下表格或 联系我们 通过发送电子邮件至 sales@migops.com
