PostgreSQL在数字后面加上零 [英] PostgreSQL adds trailing zeros to numeric

问题描述

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

我要插入的数据示例是列中的 0.75 ，存储的值是 0.750 . numeric(9,4)列的另一个示例:我插入值 12 ，并且数据库保留了 12.0000 .

我发现了以下相关问题: postgresql数值类型，不带零结尾 .但是，除了引用9.x文档(未添加尾随零)以外，它没有提供其他解决方案.在这个问题上，答案引用了文档(我也阅读过)，其中说:

数值实际存储时没有任何额外的前导或 尾随零.因此，声明的列的精度和小数位数 是最大值，而不是固定分配.

但是，就像那个问题海报一样，我看到添加了尾随零. Hibernate在日志中生成的原始插入内容不会显示此多余的行李.因此，我假设这是我未正确设置的PostgreSQL内容，只是找不到我怎么弄错了.

解决方案

如果我在这种情况下正确理解胁迫"，我想就是这样.这是来自PostgreSQL文档:

数字列的最大精度和最大小数位数 可以配置.要声明数值类型的列，请使用以下语法:

NUMERIC(precision, scale)

精度必须为正，小数位数为零或正. 或者:

NUMERIC(precision)

选择0的小数位数.指定:

NUMERIC

在没有任何精度或小数位数的情况下会创建一个列 可以存储任何精度和小数位数的数值 实施对精度的限制.这种列不会 将输入值强制转换为任何特定比例， 为数字 具有声明比例的列将把输入值强制为该比例.

强调粗体.

因此稍后在同一部分中 误导 :

数值实际存储为 ，而没有任何多余的前导或 尾随零.因此，声明的列的精度和小数位数 是最大值，而不是固定分配 .

再次强调大胆.

这对于精度部分可能是正确的，但是由于在定义比例时会强制将小数位，因此将尾随零添加到输入值中以满足小数位的定义(如果太大，我会认为它会被截断). /p>

我正在使用 precision，scale 定义来实施约束.正是在DB插入过程中，将尾随零添加到数字小数位数，这似乎支持强制转换，并且与不添加尾随零的语句产生冲突.

是否正确，选择后我必须处理代码中的问题.对我来说幸运的是，受影响的属性是BigDecimal，因此剥离尾随零很容易(尽管不够优雅).如果有人建议不要让PostgreSQL在插入时将尾随零添加到数字刻度上，那么我可以接受它们.

Recently I migrated a DB to PostgreSQL that has some columns defined as numeric(9,3) and numeric(9,4). In testing the app I have found that when data is saved to these columns there are trailing zeros being added to the value inserted. I am using Hibernate, and my logs show the correct values being built for the prepared statements.

An example of the data I am inserting is 0.75 in the numeric(9,3) column and the value stored is 0.750. Another example for the numeric(9,4) column: I insert the value 12 and the DB is holding 12.0000.

I found this related question: postgresql numeric type without trailing zeros. But it did not offer a solution other than to quote the 9.x documentation saying trailing zeros are not added. From that question, the answer quoted the docs (which I have also read) which said:

Numeric values are physically stored without any extra leading or trailing zeroes. Thus, the declared precision and scale of a column are maximums, not fixed allocations.

However, like that question poster, I see trailing zeros being added. The raw insert generated by Hibernate in the logs does not show this extra baggage. So I am assuming it is a PostgreSQL thing I have not set correctly, I just can't find how I got it wrong.

解决方案

I think this is it, if I am understanding "coerce" correctly in this context. This is from the PostgreSQL docs:

Both the maximum precision and the maximum scale of a numeric column can be configured. To declare a column of type numeric use the syntax:

NUMERIC(precision, scale)

The precision must be positive, the scale zero or positive. Alternatively:

NUMERIC(precision)

selects a scale of 0. Specifying:

NUMERIC

without any precision or scale creates a column in which numeric values of any precision and scale can be stored, up to the implementation limit on precision. A column of this kind will not coerce input values to any particular scale, whereas numeric columns with a declared scale will coerce input values to that scale.

Bold emphasis mine.

So it is misleading later in the same section:

Numeric values are physically stored without any extra leading or trailing zeroes. Thus, the declared precision and scale of a column are maximums, not fixed allocations.

Bold emphasis mine again.

This may be true of the precision part, but since the scale is being coerced when it is defined, trailing zeros are being added to the input values to meet the scale definition (and I would assume truncated if too large).

I am using precision,scale definitions for constraint enforcement. It is during the DB insert that the trailing zeros are being added to the numeric scale, which seems to support the coercion and conflicts with the statement of no trailing zeros being added.

Correct or not, I had to handle the problem in code after the select is made. Lucky for me the impacted attributes are BigDecimal so stripping trailing zeros was easy (albeit not graceful). If someone out there has a better suggestion for not having PostgreSQL add trailing zeros to the numeric scale on insert, I am open to them.

这篇关于PostgreSQL在数字后面加上零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！