PostgreSQL将数字末尾添加了零

Question

最近，我将一个包含numeric(9,3)和numeric(9,4)列定义的数据库迁移到了PostgreSQL。在测试应用程序时，我发现当数据保存到这些列中时，插入的值会添加尾随零。我正在使用Hibernate，并且我的日志显示已为预处理语句构建了正确的值。

我要插入的数据示例是在numeric(9,3)列中插入0.75，存储的值是0.750。对于numeric(9,4)列的另一个示例：我插入值12，而数据库却保存了12.0000。

我找到了这个相关的问题：postgresql numeric type without trailing zeros。但它没有提供解决方案，只是引用了9.x文档，说明不会添加尾随零。从那个问题中，回答引用了我也读过的文档，其中说：

“数字值在物理上没有任何额外的前导或尾随零。因此，列的声明精度和比例是最大值，而不是固定分配。”

然而，与该问题的发布者一样，我看到添加了尾随零。Hibernate在日志中生成的原始插入不显示这个额外的部分。因此，我认为这是一个我没有正确设置的PostgreSQL问题，只是找不到我错在哪里了。

- verbyk1924

---

你在哪个客户端看到了这些零？你试过用psql吗？ - Clodoaldo Neto

这是一个Web应用程序，我正在使用Firefox作为我的浏览器。应用服务器使用Java和Hibernate。带有尾随零的“修改”值在实际的数据库记录中可见（并在查询时以此方式返回给客户端）。 - verbyk1924

“visible in the actual DB records” 是什么意思？是指客户端显示的内容吗？我已经问过了，但你没有回答：你试过 psql 吗？ - Clodoaldo Neto

是的，我确实尝试了一个相同结果的psql查询。由于我支持的应用程序使用Java和Hibernate，这就是我需要使其工作的组合。当我提到“在实际的DB记录中可见”时，我指的是在使用工具查看表格时（在我的情况下是pgAdmin3），它所显示的内容。pgAdmin3在想要查看/编辑数据时执行查询，显示尾随零作为结果...这就是来自服务器的Web浏览器所显示的内容。 - verbyk1924

Answer 1

我认为如果我在这种情况下正确理解“强制转换”一词，那么这就是它。这来自PostgreSQL文档：

数值列的最大精度和最大比例可以进行配置。要声明类型为numeric的列，请使用以下语法：

NUMERIC(precision, scale)

精度必须是正数，比例为零或正数。 或者：

NUMERIC(precision)

选择一个0级的比例尺。具体指定:

NUMERIC

没有任何精度或比例可以创建一个列，其中可以存储任何精度和比例的数字值，最高可达实现限制的精度。这种类型的列不会强制输入值到任何特定的比例，而具有声明比例的数字列将强制输入值到该比例。

加粗强调为我的标记。

因此，在同一节中稍后说法是误导性的:

引用：

数字值在物理上没有任何额外的前导或尾随零。因此，列的声明精度和比例是最大值，而不是固定分配。

再次加粗强调为我的标记。

这可能对于精度部分是正确的，但是由于比例定义时正在强制执行比例，因此添加了尾随零以满足比例定义的输入值（如果太大，则可能被截断）。

我正在使用精度、比例定义进行约束强制执行。在进行DB插入时，数字比例正在添加尾随零，这似乎支持强制执行并与不添加尾随零的声明相冲突。

无论正确与否，我必须在选择后在代码中处理问题。幸运的是，受影响的属性是BigDecimal，因此去除尾随零很容易（虽然不太优雅）。如果有人有更好的建议，以避免PostgreSQL在插入时添加数字比例的尾随零，我愿意接受。

- verbyk1924

Answer 2

如果您指定了精度和比例，Pg会填充到该精度和比例。

regress=> SELECT '0'::NUMERIC(8,4);
 numeric 
---------
  0.0000
(1 row)

这是无法关闭的。它仍然是相同的数字，精度由类型而非值确定。

如果您想要由值定义精度，则必须使用无限定的numeric：

regress=> SELECT '0'::NUMERIC, '0.0'::NUMERIC;
 numeric | numeric 
---------+---------
       0 |     0.0                                                                                                                                                             
(1 row)                                                                                                                                                                        

- Craig Ringer

---

我对PostgreSQL还不熟悉，所以如果我在数据库中定义了一个numeric(9,4)类型的列（例如后面跟随的num_col），并执行纯SQL插入（insert into blah(pk, num_col) values(1, 12);）……当我使用SELECT查看时，我的数字12现在变成了12.0000（例如：select num_col from blah;）？尺度和精度是在列本身上定义的（作为我的限制），而不是在选择期间使用的SQL中定义的。在插入期间，尺度通过尾随零填充到定义的限制。对我来说，插入12应该存储12（而不是12.0000），SQL查询应该返回相同的值？ - verbyk1924

Answer 3

你可以使用 PostgreSQL v13 中的 trim_scale 函数来去除训练零。这将减少数字的存储大小。

- Laurenz Albe