sql - 如何理解 PosgreSQL 中的 to_number 格式

Question

根据我对格式化文档的阅读，我没有从 PostgreSQL 的函数“to_number”中得到我期望的行为。所以我可能读错了。有人可以解释一下，以便我知道在其他类似情况下会发生什么吗？

-- I find this intuitive:
# select to_number( '12,345.67', '99999.999') ;
 to_number 
-----------
  12345.67

-- I find this surprising:
# select to_number( '12,345.67', '99999.99') ;
 to_number 
-----------
   12345.6

-- EDIT: I found this surprising new variation:
# select to_number( '12,345.67', '999999.99') ;
to_number 
-----------
  12345.67

为什么我的最后百分之一数字在第二种情况下丢失了？

编辑：似乎这个问题与舍入或在我的格式中小数点右侧出现多少位无关。相反，问题与格式包含的字符总数有关，因此与被解析的字符总数有关。我认为最终的完整答案将是 mu 太短发布的内容略有不同。

在实践中，我总是可以返回比我认为需要的更多的数字。但这并不是很令人满意。它可能有一天会咬我。注意：格式中的“9”与“0”不是问题。它们在 to_number 中的行为相同，我觉得这有点令人惊讶……但在上面的链接中清楚地记录了。

标签： sqlpostgresqlformatting

Answer 1

我将首先感谢穆。他的回答显然很有帮助。但是我发布了一个单独的答案，因为我认为他所说的答案错过了答案的重要部分。

我没有看过任何 PostgreSQL 代码，所以我的答案纯粹来自对其行为的观察。当我创建我的第一个格式时，我隐含地假设如下：

# My pseudocode for select to_number( '12,345.67', '99999.99') ;
# I guessed PostgreSQL would do this:
1. Parse up to 5 digits
2. [optionally] find a decimal
3. [optionally] if decimal was found, find up to 2 more digits

in this example:
1. Up to five digits: 12345
2. Decimal: yes
3. Two more digits: 67
4. All together: 12345.67

# But in fact what it does is closer to this:
1. Parse up to 8 characters
2. Find the first decimal point in the parsed characters
3. In the set of parsed characters, find up to 5 characters before the decimal 
4. In the set of parsed characters, find up to 2 characters after the decimal.

in this example:
1. Up to 8 characters: 12,345.6
2. First decimal: the penultimate character
3. Before decimal: 12345  
4. After decimal:  6
5. All together: 12345.6

因此，我的问题基本上是PostgreSQL 只解析 8 个字符，但我传入了 9 个字符。因此解决方案是：

# Mu's suggestion: include comma in the format. Now the format is 9 characters.
# This way it parses all 9 characters:
select to_number('12,345.67', '99,999.99');
 to_number 
-----------
  12345.67

# Or include another character before the decimal
# This way it also parses 9 characters before limiting to 5.2:
select to_number( '12,345.67', '999999.99') ;
 to_number 
-----------
  12345.67

# Or include another character after the decimal
# This way it parses 9 characters before limiting to 5.3:
select to_number( '12,345.67', '99999.999') ;
 to_number 
-----------
  12345.67

一旦你这样看待它，就会很清楚为什么其他难以理解的退化案例会像它们那样工作：

# like this one work as they do:
select to_number('1x2,3yz45.67', '9999999.9999');
 to_number 
-----------
  12345.67

select to_number('12.3.45.67', '9999999.9999');
 to_number 
-----------
   12.3456

我不确定我是否会指定这样的行为。但现在更清楚会发生什么。